PSQL schema cloner

schemaClone.sql

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
$$;