This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- add FK constraint | |
-- set the search path to the dest schema so that FK contraints are defined with the source_schema qualifier | |
-- i.e. source_schema = public, desc_schema = schema_copy | |
-- then the FOREIGN KEY (company_id) REFERENCES companies(id) will be qualified as | |
-- FOREIGN KEY (company_id) REFERENCES public.companies(id) | |
SET SEARCH_PATH TO dest_schema; | |
FOR qry IN | |
SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) | |
|| ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' | |
|| replace(pg_get_constraintdef(ct.oid), source_schema, dest_schema) || ';' |