postgresql clone schema

-- Function: clone_schema(text, text)

DROP FUNCTION clone_schema(text, text);

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text)
  RETURNS void AS
$BODY$
 
DECLARE
  seq RECORD;
  table_ text;
  buffer text;
  name_ text;
  tablefrom_ text;
  columnfrom_ text;
  tableto_ text;
  columnto_ text;
  
  column_ text;
  default_ text;
  seq_id_start text;
BEGIN
  EXECUTE 'CREATE SCHEMA ' || dest_schema || ' AUTHORIZATION ' || dest_schema;
 
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR seq IN
    SELECT * FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || seq.sequence_name;
    EXECUTE 'ALTER SEQUENCE ' || dest_schema || '.' || seq.sequence_name || ' OWNER TO ' || dest_schema;
    EXECUTE 'SELECT last_value FROM ' || source_schema || '.' || seq.sequence_name INTO seq_id_start;
    EXECUTE 'ALTER SEQUENCE ' || dest_schema || '.' || seq.sequence_name || ' INCREMENT BY ' || seq.increment || ' RESTART WITH '|| seq_id_start;   
  END LOOP;
 
  FOR table_ IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || table_;
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || table_ || ' INCLUDING ALL)';
    EXECUTE 'ALTER TABLE ' || buffer || ' OWNER TO ' || dest_schema;
  END LOOP;

  FOR table_ IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || table_;
    FOR name_, tablefrom_, columnfrom_, tableto_, columnto_ IN
      SELECT DISTINCT tc.constraint_name as name_, tc.table_name as tablefrom_, kcu.column_name as columnto_, ccu.table_name AS tableto_, ccu.column_name AS columnto_ 
      FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
      JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
      WHERE constraint_type = 'FOREIGN KEY' AND tc.constraint_schema = source_schema AND kcu.constraint_schema = source_schema AND ccu.constraint_schema = source_schema AND tc.table_name=table_
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ADD CONSTRAINT ' || name_ || ' FOREIGN KEY (' || columnfrom_ || ') REFERENCES ' || dest_schema || '.' || tableto_ || ' (' || columnto_ || ') DEFERRABLE INITIALLY DEFERRED';
    END LOOP;
   END LOOP;

  FOR table_ IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || table_;
    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 = table_ AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
    
    EXECUTE 'INSERT INTO ' || buffer || '(SELECT * FROM ' || source_schema || '.' || table_ || ')';
  END LOOP;
  EXECUTE 'GRANT ALL ON SCHEMA ' || dest_schema || ' TO ' || dest_schema;
  EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || dest_schema || ' GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ' || dest_schema;
END;
 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
--ALTER FUNCTION clone_schema(text, text)
--  OWNER TO postgres;

Line 23 assumes that the destination schema will be a role, as do lines 69-70.

You can find my clone_schema() version which is better aligned with the original clone_schema() in that it doesn't copy the data, only structure. It also has foreign key support, but it retrains deferrable/cascade settings. It also has trigger support which you might be interested in adding.

https://gist.github.com/rocksfrow/8a0bd1ce031478b4530b

Skip to content

Search…
All gists
GitHub
New gist
@yogi-oc
Star 6
Fork 1
@rocksfrowrocksfrow/clone_schema_fn.sql
Last active 29 days ago
Embed

<script src="https://gist.github.com/rocksfrow/8a0bd1ce031478b4530b.js"></script>

Download ZIP
Code Revisions 2 Stars 6 Forks 1
enhanced postgresql clone_schema() function with trigger + foreign key support
Raw
clone_schema_fn.sql
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$

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;

$$ LANGUAGE plpgsql VOLATILE;
@rocksfrow
Owner
rocksfrow commented on Apr 11, 2015
Original source: https://wiki.postgresql.org/wiki/Clone_schema

I've enhanced the original function with the following:

trigger support
foreign key support
Usage:

begin;
select clone_schema('schema','newschema');
commit;
NOTE: this function is only intended to copy the structure of a schema, not the data.
@rosin1408
rosin1408 commented on Mar 28, 2016
tks, this works well for me
@yogi-oc

yogi-oc commented just now
This script doesn't do constraints properly. It refers back to the public schema as seen here:
-- Table: tenant5.archived_dataset_file

-- DROP TABLE tenant5.archived_dataset_file;

CREATE TABLE tenant5.archived_dataset_file
(
archived_dataset_file_id integer NOT NULL DEFAULT nextval('archived_dataset_file_archived_dataset_file_id_seq'::regclass),
name character varying(255),
dataset_id integer,
export_format_id integer,
file_reference character varying(1000),
run_time integer,
file_size integer,
date_created timestamp(6) without time zone,
owner_id integer,
CONSTRAINT archived_dataset_file_pkey PRIMARY KEY (archived_dataset_file_id),
CONSTRAINT fk_archived_reference_dataset FOREIGN KEY (dataset_id)
REFERENCES public.dataset (dataset_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_archived_reference_export_f FOREIGN KEY (export_format_id)
REFERENCES public.export_format (export_format_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
ALTER TABLE tenant5.archived_dataset_file
OWNER TO clinica;
@yogi-oc

Write Preview

Leave a comment
Attach files by dragging & dropping, Choose Files selecting them, or pasting from the clipboard.
Styling with Markdown is supported
Comment
Contact GitHub API Training Shop Blog About
© 2017 GitHub, Inc. Terms Privacy Security Status Help