Last active
February 20, 2023 21:57
-
-
Save QuittyMR/94be258d304d25941fd7aa356659ecc1 to your computer and use it in GitHub Desktop.
PSQL schema cloner
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
create view public.sys_get_references as SELECT tc.table_schema AS referencing_schema, | |
tc.constraint_name, | |
tc.table_name AS referencing_table, | |
kcu.column_name AS referencing_column, | |
pg_get_constraintdef(pc.oid) AS constraint_text | |
FROM information_schema.table_constraints tc | |
JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text | |
JOIN pg_constraint pc ON pc.conname = tc.constraint_name::name | |
WHERE tc.constraint_type::text = 'FOREIGN KEY'::text; | |
create or replace procedure public._clone_schema(target text) | |
language plpgsql as | |
$$ | |
declare | |
tbl record; | |
v record; | |
c record; | |
BEGIN | |
EXECUTE (select 'drop schema if exists "' || target || '" cascade'); | |
EXECUTE (select 'create schema "' || target || '"'); | |
FOR tbl in (select tablename from pg_tables where schemaname = 'public' and tableowner not like '%rds%') | |
loop | |
EXECUTE (SELECT 'CREATE TABLE if not exists "' || target || '".' || tbl.tablename::text || | |
'( like public.' || | |
tbl.tablename::text || ' including all)'); | |
raise INFO 'created table %', tbl.tablename; | |
end loop; | |
for v in (select viewname, | |
case | |
when definition ilike '%public.%' then | |
replace(definition, 'public.', '"' || target || '".') | |
else definition | |
end as rpl_definition | |
from pg_views | |
where schemaname = 'public' | |
and viewowner not ilike '%rds%') | |
loop | |
EXECUTE (select 'create or replace view "' || target || '".' || v.viewname || ' as ' || v.rpl_definition); | |
raise INFO 'created view %s', v.viewname; | |
end loop; | |
for c in (select referencing_table, | |
constraint_name, | |
case | |
when constraint_text ilike '%.%' | |
then regexp_replace(constraint_text, '(.+ ).*\.(.+)', '\1'|| '"' || target || '".' || '\2') | |
else replace(constraint_text, 'REFERENCES ', 'REFERENCES "' || target || '".') end as rpl_constraint_text | |
from public.sys_get_references | |
where referencing_schema = 'public' | |
group by rpl_constraint_text, referencing_table, constraint_name) | |
loop | |
execute (select 'alter table "' || target || '".' || c.referencing_table || ' add constraint "' || | |
c.constraint_name || '" ' || c.rpl_constraint_text); | |
end loop; | |
END | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment