Created
February 14, 2017 12:38
-
-
Save mlibrodo/6f246c483e650dc716eba752a9d3c79a to your computer and use it in GitHub Desktop.
makes sure that the FK constraints are pointing to the tables in the cloned schema
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) || ';' | |
FROM pg_constraint ct | |
JOIN pg_class rn ON rn.oid = ct.conrelid | |
WHERE connamespace = src_oid | |
AND rn.relkind = 'r' | |
AND ct.contype = 'f' | |
LOOP | |
EXECUTE qry; | |
END LOOP; | |
SET SEARCH_PATH TO source_schema; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Without the modification above the cloned schema will create the constraint against the table in the source schema.. notice confrelid are all the same.
Compared to this which points the relation to another relid