Posgresql: delete all custom defined tables, functions, procedures and enums on current database
I am trying to create a procedure to delete all custom defined elements in the current database I'm operating with. That includes:
I don't want to drop schema and recreate it: the idea is just to delete these elements.
This is an attempt I did taking code from https://dba.stackexchange.com/questions/122742/how-to-drop-all-of-my-functions-in-postgresql and https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database .
drop procedure if exists reset_db;
create procedure reset_db() language plpgsql AS $$
-- Drop all functions and procedures
declare _sql text;
select into _sql
string_agg(format('DROP %s %s;'
, case prokind
when 'f' then 'FUNCTION'
when 'p' then 'PROCEDURE'
where pronamespace = current_schema()::regnamespace
if _sql is not null then raise notice '%', _sql; -- debug / check first execute _sql; -- uncomment payload once you are sure else raise notice 'No fuctions found in schema %', quote_ident(_schema); end if;
-- Drop custom types. Don't know how to iterate here to automate it.
drop type LevelEnum cascade;
drop type StatusEnum cascade;
-- Drop all tables
for r in (select tablename from pg_tables where schemaname = current_schema()) loop
execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
Currently this seems to delete other functions apart of the ones I defined for my current DB. Also I don't know how to loop through the custom defined types as the enums to delete them.
Any help will be welcome.
The only good way to do that is to have all the objects you want to get rid of be owned by a certain role. Then you can run
DROP OWNED BY owning_role;