drop multiple tables using do block in PostgreSQL
Sometimes we want to drop all the tables in a database with a particular naming convention, below is the simple SQL script which drop all the tables in a database with naming convention like “test_temp_****”.
DO
$$
DECLARE
query text;
rec record;
BEGIN
query='select schemaname, tablename from pg_tables where tablename like ''test_temp_%''';
FOR rec IN EXECUTE query
LOOP
RAISE NOTICE 'drop table %.%',rec.schemaname,rec.tablename;
EXECUTE 'drop table ' || rec.schemaname||'.'||rec.tablename;
END LOOP;
END;
$$;
Viswamitra's blog 