Delete all tables, functions and triggers from all schemata in a PostgreSQL database

purge-schemata.sql

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
$$;