Drop all database objects for PostgreSQL

drop_all_objects.sql

CREATE OR REPLACE FUNCTION drop_all_objects() RETURNS VOID AS 
$$

DECLARE
 rd_object RECORD;
 v_idx_statement VARCHAR(500); 
 
 BEGIN
 	    -- 1. Dropping all stored functions
 	    RAISE NOTICE '%', 'Dropping all stored functions...';
FOR rd_object IN (SELECT FORMAT('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) AS functionDef
		FROM pg_proc p
		INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
		WHERE ns.nspname = current_schema AND p.proname <> 'drop_all_objects') LOOP
		 v_idx_statement = 'DROP FUNCTION ' || rd_object.functionDef;
		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
		 RAISE NOTICE '%', 'Done. Droped all stored functions...';
		
		 -- 2. Dropping all views
		 RAISE NOTICE '%', 'Dropping all views...';
FOR rd_object IN (
		SELECT viewname
		FROM pg_views
		WHERE schemaname = current_schema ) LOOP
		 v_idx_statement = 'DROP VIEW ' || current_schema || '."' || rd_object.viewname || '"';
		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
		 RAISE NOTICE '%', 'Done. Droped all views...';
		
		 -- 3. Dropping all table objects
		 RAISE NOTICE '%', 'Dropping all table objects...';
		 -- Drop child partitions first and then the base tables.
FOR rd_object IN (WITH child AS (
SELECT c.relname AS tablename, 0 AS parent
FROM pg_inherits
JOIN pg_class AS c ON (inhrelid=c.oid)
WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = current_schema))

SELECT * FROM child 
UNION ALL
SELECT tablename, 1 AS parent FROM pg_tables WHERE schemaname = current_schema AND tablename NOT IN (SELECT inn.tablename FROM child inn) ORDER BY parent) LOOP
		 v_idx_statement = 'DROP TABLE IF EXISTS ' || current_schema || '."' || rd_object.tablename || '" CASCADE';
		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
		 RAISE NOTICE '%', 'Done. Droped all table objects...';
		
		 -- 4. Dropping all Sequence objects
		 RAISE NOTICE '%', 'Dropping all Sequence objects...';
FOR rd_object IN (
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = current_schema
		) LOOP
		
		 v_idx_statement = 'DROP SEQUENCE ' || current_schema || '.' || rd_object.sequence_name;
		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
		 RAISE NOTICE '%', 'Done. Droped all sequences...';
 
 END;
$$ LANGUAGE plpgsql;

SELECT *
FROM drop_all_objects();
DROP FUNCTION drop_all_objects();