Sunday, December 1, 2013

Postgres Create drop constraint script with the help of function


Create drop script for constraints in postgres sql :

CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
language PLPGSQL AS $$
DECLARE
   Vtbl varchar(150);
   Vqrys varchar(150);
   Vcol varchar(150);
   Vcon varchar(150);
   Vqryd varchar(300);
   Vqrya varchar(300);
   Vsch varchar(150);
   VqryAdd varchar(30000) = '';
   VqryDrp varchar(30000) = '';
   Vrtn varchar;

   Ctbl_list refcursor;
   Ctblcon information_schema.table_constraints %rowtype;
   Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
 open Ctbl_list for execute 'SELECT *
    FROM information_schema.table_constraints tc
    right JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_catalog = ccu.constraint_catalog
      AND tc.constraint_schema = ccu.constraint_schema
      AND tc.constraint_name = ccu.constraint_name
      and ccu.table_name in ('||quote_literal(tbl_name)||')
      and ccu.column_name in ('||quote_literal(col_name)||')
    WHERE lower(tc.constraint_type) in (''foreign key'');' ;
 loop fetch next from Ctbl_list into Ctblcon ;
 exit when not found;
    Vtbl = Ctblcon.table_name;
    Vcon = Ctblcon.constraint_name;
    Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
    Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
    VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
    raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
    Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
  EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$$; 
 
Execute the above function by adding table and its column name on which we need to find the dependency
SELECT * FROM fn_get_fk('language','language_id')

This script will be helpful in finding parent child relationship of tables depending foreign keys or you can modify the function accordingly.......................

1 comment:

  1. A great,interesting and useful blog post to read about the many aspects of SSIS and Postgresql which have created quite a stirr in the IT world ever since their inception.

    SSIS Postgresql Write

    ReplyDelete

web stats