Saturday, 26 October 2019 21:58

Postgres Drop Structures

Written by http://blog.mclaughlinsoftware.com/2019/10/27/postgres-drop-structures/
Rate this item
(0 votes)

While building my PostgreSQL environment for the class, I had to write a couple utilities. They do the following:

  1. Drops all the tables from a schema.
  2. Drops all the sequences from a schema
  • that aren’t tied to an _id column with a SERIAL data type.
  • Drops all the functions and procedures (qualified as routines) from a schema.
  • Drops all the triggers from a schema.
  • The following gives you the code for all four files: drop_tables.sql, drop_sequences.sql, drop_routines.sql, and drop_triggers.sql.

    • The drop_tables.sql Script:
    • /* Verify all tables present. */SELECT table_nameFROM information_schema.tablesWHERE table_catalog = current_setting('videodb.catalog_name')AND table_schema ='public';DO $$DECLARE/* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD;/* Declare a cursor. */ table_cursor CURSOR FOR SELECT table_name FROM information_schema.tablesWHERE table_catalog = current_setting('videodb.catalog_name')AND table_schema ='public';BEGIN/* Open the cursor. */ OPEN table_cursor; LOOP /* Fetch table names. */ FETCH table_cursor INTO row;/* Exit when no more records are found. */ EXIT WHENNOT FOUND;/* Concatenate together a DDL to drop the table with prejudice. */ sql :='DROP TABLE IF EXISTS '||row.table_name||' CASCADE';/* Execute the DDL statement. */ EXECUTE sql;END LOOP;/* Close the cursor. */ CLOSE table_cursor;END;$$;/* Verify all tables are dropped. */SELECT table_nameFROM information_schema.tablesWHERE table_catalog = current_setting('videodb.catalog_name')AND table_schema ='public';

      /* Verify all tables present. */ SELECT table_name FROM information_schema.tables WHERE table_catalog = current_setting('videodb.catalog_name') AND table_schema = 'public'; DO $$ DECLARE /* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD; /* Declare a cursor. */ table_cursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_catalog = current_setting('videodb.catalog_name') AND table_schema = 'public'; BEGIN /* Open the cursor. */ OPEN table_cursor; LOOP /* Fetch table names. */ FETCH table_cursor INTO row; /* Exit when no more records are found. */ EXIT WHEN NOT FOUND; /* Concatenate together a DDL to drop the table with prejudice. */ sql := 'DROP TABLE IF EXISTS '||row.table_name||' CASCADE'; /* Execute the DDL statement. */ EXECUTE sql; END LOOP; /* Close the cursor. */ CLOSE table_cursor; END; $$; /* Verify all tables are dropped. */ SELECT table_name FROM information_schema.tables WHERE table_catalog = current_setting('videodb.catalog_name') AND table_schema = 'public';

    • The drop_sequences.sql script:
    • /* Verify all tables present. */SELECT sequence_nameFROM information_schema.sequencesWHERE sequence_catalog = current_setting('videodb.catalog_name')AND sequence_schema ='public';DO $$DECLARE/* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD;/* Declare a cursor. */ sequence_cursor CURSOR FOR SELECT sequence_name FROM information_schema.sequences WHERE sequence_catalog = current_setting('videodb.catalog_name')AND sequence_schema ='public';BEGIN/* Open the cursor. */ OPEN sequence_cursor; LOOP /* Fetch table names. */ FETCH sequence_cursor INTO row;/* Exit when no more records are found. */ EXIT WHENNOT FOUND;/* Concatenate together a DDL to drop the table with prejudice. */ sql :='DROP SEQUENCE IF EXISTS '||row.sequence_name;/* Execute the DDL statement. */ EXECUTE sql;END LOOP;/* Close the cursor. */ CLOSE sequence_cursor;END;$$;/* Verify all tables are dropped. */SELECT sequence_nameFROM information_schema.sequencesWHERE sequence_catalog = current_setting('videodb.catalog_name')AND sequence_schema ='public';

      /* Verify all tables present. */ SELECT sequence_name FROM information_schema.sequences WHERE sequence_catalog = current_setting('videodb.catalog_name') AND sequence_schema = 'public'; DO $$ DECLARE /* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD; /* Declare a cursor. */ sequence_cursor CURSOR FOR SELECT sequence_name FROM information_schema.sequences WHERE sequence_catalog = current_setting('videodb.catalog_name') AND sequence_schema = 'public'; BEGIN /* Open the cursor. */ OPEN sequence_cursor; LOOP /* Fetch table names. */ FETCH sequence_cursor INTO row; /* Exit when no more records are found. */ EXIT WHEN NOT FOUND; /* Concatenate together a DDL to drop the table with prejudice. */ sql := 'DROP SEQUENCE IF EXISTS '||row.sequence_name; /* Execute the DDL statement. */ EXECUTE sql; END LOOP; /* Close the cursor. */ CLOSE sequence_cursor; END; $$; /* Verify all tables are dropped. */ SELECT sequence_name FROM information_schema.sequences WHERE sequence_catalog = current_setting('videodb.catalog_name') AND sequence_schema = 'public';

    • The drop_routines.sql script:
    • /* Verify all tables present. */SELECT routine_name, routine_typeFROM information_schema.routinesWHERE specific_catalog = current_setting('videodb.catalog_name')AND specific_schema ='public';DO $$DECLARE/* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD;/* Declare a cursor. */ routine_cursor CURSOR FOR SELECT routine_name , routine_type FROM information_schema.routines WHERE specific_catalog = current_setting('videodb.catalog_name')AND routine_schema ='public';BEGIN/* Open the cursor. */ OPEN routine_cursor; LOOP /* Fetch table names. */ FETCH routine_cursor INTO row;/* Exit when no more records are found. */ EXIT WHENNOT FOUND;/* Concatenate together a DDL to drop the table with prejudice. */ sql :='DROP '||row.routine_type||' IF EXISTS '||row.routine_name;/* Execute the DDL statement. */ EXECUTE sql;END LOOP;/* Close the cursor. */ CLOSE routine_cursor;END;$$;/* Verify all tables are dropped. */SELECT routine_name, routine_typeFROM information_schema.routinesWHERE specific_catalog ='videodb'AND specific_schema ='public';

      /* Verify all tables present. */ SELECT routine_name , routine_type FROM information_schema.routines WHERE specific_catalog = current_setting('videodb.catalog_name') AND specific_schema = 'public'; DO $$ DECLARE /* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD; /* Declare a cursor. */ routine_cursor CURSOR FOR SELECT routine_name , routine_type FROM information_schema.routines WHERE specific_catalog = current_setting('videodb.catalog_name') AND routine_schema = 'public'; BEGIN /* Open the cursor. */ OPEN routine_cursor; LOOP /* Fetch table names. */ FETCH routine_cursor INTO row; /* Exit when no more records are found. */ EXIT WHEN NOT FOUND; /* Concatenate together a DDL to drop the table with prejudice. */ sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name; /* Execute the DDL statement. */ EXECUTE sql; END LOOP; /* Close the cursor. */ CLOSE routine_cursor; END; $$; /* Verify all tables are dropped. */ SELECT routine_name , routine_type FROM information_schema.routines WHERE specific_catalog = 'videodb' AND specific_schema = 'public';

    • The drop_triggers.sql script:
    • /* Verify all tables present. */SELECT trigger_nameFROM information_schema.triggersWHERE trigger_catalog = current_setting('videodb.catalog_name')AND trigger_schema ='public';DO $$DECLARE/* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD;/* Declare a cursor. */ trigger_cursor CURSOR FOR SELECT trigger_name FROM information_schema.triggers WHERE trigger_catalog = current_setting('videodb.catalog_name')AND trigger_schema ='public';BEGIN/* Open the cursor. */ OPEN trigger_cursor; LOOP /* Fetch table names. */ FETCH trigger_cursor INTO row;/* Exit when no more records are found. */ EXIT WHENNOT FOUND;/* Concatenate together a DDL to drop the table with prejudice. */ sql :='DROP TRIGGER IF EXISTS '||row.trigger_name;/* Execute the DDL statement. */ EXECUTE sql;END LOOP;/* Close the cursor. */ CLOSE trigger_cursor;END;$$;/* Verify all tables are dropped. */SELECT trigger_nameFROM information_schema.triggersWHERE trigger_catalog = current_setting('videodb.catalog_name')AND trigger_schema ='public';

      /* Verify all tables present. */ SELECT trigger_name FROM information_schema.triggers WHERE trigger_catalog = current_setting('videodb.catalog_name') AND trigger_schema = 'public'; DO $$ DECLARE /* Declare an indefinite length string and record variable. */ sql VARCHAR; row RECORD; /* Declare a cursor. */ trigger_cursor CURSOR FOR SELECT trigger_name FROM information_schema.triggers WHERE trigger_catalog = current_setting('videodb.catalog_name') AND trigger_schema = 'public'; BEGIN /* Open the cursor. */ OPEN trigger_cursor; LOOP /* Fetch table names. */ FETCH trigger_cursor INTO row; /* Exit when no more records are found. */ EXIT WHEN NOT FOUND; /* Concatenate together a DDL to drop the table with prejudice. */ sql := 'DROP TRIGGER IF EXISTS '||row.trigger_name; /* Execute the DDL statement. */ EXECUTE sql; END LOOP; /* Close the cursor. */ CLOSE trigger_cursor; END; $$; /* Verify all tables are dropped. */ SELECT trigger_name FROM information_schema.triggers WHERE trigger_catalog = current_setting('videodb.catalog_name') AND trigger_schema = 'public';

    You can create a cleanup_catalog.sql script to call all four in sequence, like the following:

    \i /home/student/Data/cit225/postgres/lib/utility/drop_tables.sql\i /home/student/Data/cit225/postgres/lib/utility/drop_sequences.sql\i /home/student/Data/cit225/postgres/lib/utility/drop_routines.sql\i /home/student/Data/cit225/postgres/lib/utility/drop_triggers.sql

    \i /home/student/Data/cit225/postgres/lib/utility/drop_tables.sql \i /home/student/Data/cit225/postgres/lib/utility/drop_sequences.sql \i /home/student/Data/cit225/postgres/lib/utility/drop_routines.sql \i /home/student/Data/cit225/postgres/lib/utility/drop_triggers.sql

    The nice thing about this approach is that you won’t see any notices when tables, sequences, routines, or triggers aren’t found. It’s a clean approach to cleaning the schema for a testing environment.

    Read 28 times

    Leave a comment

    Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.