Skip to main content

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches:

  1. Drop Schema with cascade all and re-create it again. 
  2. In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so,
Create function which would do the task and then drop that function too.

---


CREATE OR REPLACE FUNCTION drop_DB_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_db_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.tablename;
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 ' || current_schema || '.' || rd_object.tablename;
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_DB_objects();

DROP FUNCTION drop_DB_objects();

Comments

  1. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of PostgreSQL Server Community.

    I have also prepared one article about, Populate script to drop all functions in PostgreSQL.
    You can also visit my article, your comments and reviews are most welcome.

    http://www.dbrnd.com/2016/02/postgresql-populate-drop-function-script-with-the-type-of-parameters/

    ReplyDelete
  2. Thanks this helped a lot.

    ReplyDelete

Post a Comment

Popular posts from this blog

Some facts and Figures of WCF

SOAP Message in WCF: 1.        The max size of SOAP message in WCF is 9,223,372,036,854,775,807 bytes. Including metadata. 2.        For actual user data we can use 2,147,483,647 bytes out of it. 3.        With default setting WCF uses only 65536 bytes. 4.        We can change it by setting maxReceivedMessageSize in clients app.config file.    5.        So selection of data types in Data Contract and Data table will matter a lot! 6.       Reference :   http://blogs.msdn.com/drnick/archive/2006/03/16/552628.aspx          http://blogs.msdn.com/drnick/archive/2006/03/10/547568.aspx       “Amazing blog for WCF!” Data Contract: 1.        By Default WCF can serialize 65536 DataMember. 2.        We can change it to max  2147483646. 3.       How?  Please go to: http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/4a81a226-175b-41d3-864a-181792c71ffe Tracing WCF service message: 1.       Enable message log of WCF: http://www.avingtonsolutions.com/blog/post/2008/07/25/Tracing-SOAP-Message

Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script : -------------------------------------------------------------------------------------------------------------------------- #!/bin/sh rm -rf /tmp/vacuum_analyze_MyData.sql dbname="mydata" username="mydatadatamart" namespace="mydatadatamart" # Vacuum only those tables which are fragmented over 30% # Analyze tables only if they are not already # Process CMF and Groups Master table every time as they are master Tables=`psql $dbname $username << EOF SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList   FROM ( SELECT *,       n_dead_tup > av_threshold AS "av_needed",       CASE WHEN reltuples > 0      THEN round(100.0 * n_dead_tup / (reltuples))       ELSE 0       END AS pct_dead FROM (SELECT C.relname AS TableName, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, pg_stat_get_tuples_updated(