Skip to content

Instantly share code, notes, and snippets.

@sunsided

sunsided/purge-schemata.sql

Last active August 24, 2024 18:56
Show Gist options
  • Save sunsided/1e037f0a125fcaf65bae9a2de28e30d2 to your computer and use it in GitHub Desktop.
Save sunsided/1e037f0a125fcaf65bae9a2de28e30d2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sunsided revised this gist Aug 24, 2024. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion purge-schemata.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -27,4 +27,5 @@ DO $$
    END LOOP;

    END LOOP;
    END $$;
    END
    $$;
  2. sunsided created this gist Aug 24, 2024.
    30 changes: 30 additions & 0 deletions purge-schemata.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,30 @@
    DO $$
    DECLARE
    r RECORD;
    s RECORD;
    BEGIN
    -- Loop through all non-system schemas
    FOR s IN (SELECT schema_name FROM information_schema.schemata
    WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'public')
    AND schema_name NOT LIKE 'pg_%') LOOP

    -- Drop all tables in the schema
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = s.schema_name) LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(s.schema_name) || '.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;

    -- Drop all functions in the schema
    FOR r IN (SELECT proname, oidvectortypes(proargtypes) as args
    FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = s.schema_name) LOOP
    EXECUTE 'DROP FUNCTION IF EXISTS ' || quote_ident(s.schema_name) || '.' || quote_ident(r.proname) || '(' || r.args || ')' || ' CASCADE';
    END LOOP;

    -- Drop all triggers in the schema
    FOR r IN (SELECT event_object_table, trigger_name
    FROM information_schema.triggers WHERE trigger_schema = s.schema_name) LOOP
    EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(r.trigger_name) || ' ON ' || quote_ident(s.schema_name) || '.' || quote_ident(r.event_object_table) || ' CASCADE';
    END LOOP;

    END LOOP;
    END $$;