PostgreSQL function to delete a batch of tables at once, including by wildcard. Beware.
SELECT footgun('schema', 'tablename');
CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS | |
$$ | |
DECLARE | |
row record; | |
BEGIN | |
FOR row IN | |
SELECT | |
table_schema, | |
table_name | |
FROM | |
information_schema.tables | |
WHERE | |
table_type = 'BASE TABLE' | |
AND | |
table_schema = _schema | |
AND | |
table_name ILIKE (_parttionbase || '%') | |
LOOP | |
EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE'; | |
RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name); | |
END LOOP; | |
END; | |
$$; |