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

Posted in Linux,Postgres,PostgreSQL,sql,Unix

Tagged with , , ,

«
»