N3cy9G6z

1CREATE OR REPLACE FUNCTION drop_all_objects() RETURNS VOID AS 
2$$
3
4DECLARE
5 rd_object RECORD;
6 v_idx_statement VARCHAR(500); 
7 
8 BEGIN
9 	    -- 1. Dropping all stored functions
10 	    RAISE NOTICE '%', 'Dropping all stored functions...';
11FOR rd_object IN (SELECT FORMAT('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) AS functionDef
12		FROM pg_proc p
13		INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
14		WHERE ns.nspname = current_schema AND p.proname <> 'drop_all_objects') LOOP
15		 v_idx_statement = 'DROP FUNCTION ' || rd_object.functionDef;
16		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
17		 RAISE NOTICE '%', 'Done. Droped all stored functions...';
18		
19		 -- 2. Dropping all views
20		 RAISE NOTICE '%', 'Dropping all views...';
21FOR rd_object IN (
22		SELECT viewname
23		FROM pg_views
24		WHERE schemaname = current_schema ) LOOP
25		 v_idx_statement = 'DROP VIEW ' || current_schema || '."' || rd_object.viewname || '"';
26		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
27		 RAISE NOTICE '%', 'Done. Droped all views...';
28		
29		 -- 3. Dropping all table objects
30		 RAISE NOTICE '%', 'Dropping all table objects...';
31		 -- Drop child partitions first and then the base tables.
32FOR rd_object IN (WITH child AS (
33SELECT c.relname AS tablename, 0 AS parent
34FROM pg_inherits
35JOIN pg_class AS c ON (inhrelid=c.oid)
36WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = current_schema))
37
38SELECT * FROM child 
39UNION ALL
40SELECT 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
41		 v_idx_statement = 'DROP TABLE IF EXISTS ' || current_schema || '."' || rd_object.tablename || '" CASCADE';
42		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
43		 RAISE NOTICE '%', 'Done. Droped all table objects...';
44		
45		 -- 4. Dropping all Sequence objects
46		 RAISE NOTICE '%', 'Dropping all Sequence objects...';
47FOR rd_object IN (
48SELECT sequence_name
49FROM information_schema.sequences
50WHERE sequence_schema = current_schema
51		) LOOP
52		
53		 v_idx_statement = 'DROP SEQUENCE ' || current_schema || '.' || rd_object.sequence_name;
54		 RAISE NOTICE '%', v_idx_statement; EXECUTE v_idx_statement; END LOOP;
55		 RAISE NOTICE '%', 'Done. Droped all sequences...';
56 
57 END;
58$$ LANGUAGE plpgsql;
59
60SELECT *
61FROM drop_all_objects();
62DROP FUNCTION drop_all_objects();