Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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;
@schinckel

This comment has been minimized.

Copy link

commented Jun 6, 2014

You should add this back to the postgres wiki: it looks to be a big improvement over my rewrite: including fixing a comment bug!

@schinckel

This comment has been minimized.

Copy link

commented Jun 6, 2014

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

@rocksfrow

This comment has been minimized.

Copy link

commented Apr 11, 2015

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

@yogi-oc

This comment has been minimized.

Copy link

commented Feb 17, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.