Category: Notes

How to drop all tables sharing the same prefix in PostgreSQL?

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)
    FROM   information_schema.tables
    WHERE  table_name LIKE '[prefix]' || '%'
    AND    table_schema NOT LIKE 'pg\_%'
LOOP
-- RAISE NOTICE '%',
  EXECUTE 'DROP TABLE ' || _tbl || ' CASCADE';
END LOOP;
END
$do$;

• Non-Cascade version:

EXECUTE 'DROP TABLE ' || _tbl;

How to drop all functions in PostgreSQL?

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql;
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$  LANGUAGE plpgsql;