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.......................
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.
ReplyDeleteSSIS Postgresql Write