Posgresql: delete all custom defined tables, functions, procedures and enums on current database

Question

I am trying to create a procedure to delete all custom defined elements in the current database I'm operating with. That includes:

• tables
• procedures
• functions
• enums

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 this question and this other one.

drop procedure if exists reset_db;
create procedure reset_db() language plpgsql AS $$
begin
 -- Drop all functions and procedures
 declare _sql text;
 begin
 select into _sql
 string_agg(format('DROP %s %s;'
 , case prokind
 when 'f' then 'FUNCTION'
 when 'p' then 'PROCEDURE'
 end
 , oid::regprocedure)
 , E'\n')
 from pg_proc
 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;
 end;
 -- Drop custom types. Don't know how to iterate here to automate it.
 drop type LevelEnum cascade;
 drop type StatusEnum cascade;
 -- Drop all tables
 declare
 r record;
 begin
 for r in (select tablename from pg_tables where schemaname = current_schema()) loop
 execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
 end loop;
 end;
end;
$$;

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.

• Emille C.: As I said I don't want to drop the schema, just delete the mentioned elements.
• mustaccio: And why, if I may ask, you don't want to drop the schema, if you drop everything in it?
• Emille C.: AFAIK I'm not dropping everything.
• user1822: What "other" functions is it dropping? Do you mean it drops functions in schemas other than current_schema? Did you try to use fully qualified names in your generated statements?

Answer 1

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;