Skip to content

Instantly share code, notes, and snippets.

@klebercode

klebercode/Clone Schema (Postgres)

Created April 6, 2020 21:18
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save klebercode/6758eb9610472eda0634d9c2f14ce1a2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save klebercode/6758eb9610472eda0634d9c2f14ce1a2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. klebercode created this gist Apr 6, 2020.
    69 changes: 69 additions & 0 deletions Clone Schema (Postgres)
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,69 @@
    CREATE OR REPLACE FUNCTION public.clone_schema(
    source_schema text,
    dest_schema text)
    RETURNS void AS
    $BODY$
    DECLARE
    object text;
    buffer text;
    default_ text;
    column_ text;
    constraint_name_ text;
    constraint_def_ text;
    trigger_name_ text;
    trigger_timing_ text;
    trigger_events_ text;
    trigger_orientation_ text;
    trigger_action_ text;
    BEGIN
    -- replace existing schema
    EXECUTE 'DROP SCHEMA IF EXISTS ' || dest_schema || ' CASCADE';
    -- create schema
    EXECUTE 'CREATE SCHEMA ' || dest_schema ;
    -- create sequences
    FOR object IN
    SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
    LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
    END LOOP;

    -- create tables
    FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
    LOOP
    buffer := dest_schema || '.' || object;
    -- create table
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
    -- fix sequence defaults
    FOR column_, default_ IN
    SELECT column_name::text, REPLACE(column_default::text, source_schema||'.', dest_schema||'.') FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '.%::regclass)'
    LOOP
    EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
    -- create triggers
    FOR trigger_name_, trigger_timing_, trigger_events_, trigger_orientation_, trigger_action_ IN
    SELECT trigger_name::text, action_timing::text, string_agg(event_manipulation::text, ' OR '), action_orientation::text, action_statement::text FROM information_schema.TRIGGERS WHERE event_object_schema=source_schema and event_object_table=object GROUP BY trigger_name, action_timing, action_orientation, action_statement
    LOOP
    EXECUTE 'CREATE TRIGGER ' || trigger_name_ || ' ' || trigger_timing_ || ' ' || trigger_events_ || ' ON ' || buffer || ' FOR EACH ' || trigger_orientation_ || ' ' || trigger_action_;
    END LOOP;
    END LOOP;
    -- reiterate tables and create foreign keys
    FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
    LOOP
    buffer := dest_schema || '.' || object;
    -- create foreign keys
    FOR constraint_name_, constraint_def_ IN
    SELECT conname::text, REPLACE(pg_get_constraintdef(pg_constraint.oid), source_schema||'.', dest_schema||'.') FROM pg_constraint INNER JOIN pg_class ON conrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE contype='f' and relname=object and nspname=source_schema
    LOOP
    EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;
    END LOOP;
    END LOOP;

    END;

    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

    select clone_schema('SOURCE','DESTINATION');