MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Postgres Drop Structures

with one comment

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.
  3. Drops all the functions and procedures (qualified as routines) from a schema.
  4. 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_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_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_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_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

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.

Written by maclochlainn

October 27th, 2019 at 3:58 pm