테이블

SELECT STRING_AGG('drop table if exists ' || tablename || ' cascade;', E'\\\\n' ORDER BY tablename)
  FROM pg_tables
 WHERE tablename !~ '(spatial|pg|sql)_.+'
;

프로시저

SELECT STRING_AGG('drop procedure if exists ' || proname || ';', E'\\\\n')
  FROM pg_proc
 WHERE prokind = 'p'
;