Skip to content

Instantly share code, notes, and snippets.

@hielkehoeve
Created February 5, 2014 06:58
Show Gist options
  • Save hielkehoeve/8818562 to your computer and use it in GitHub Desktop.
Save hielkehoeve/8818562 to your computer and use it in GitHub Desktop.
-- 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
Copy link

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
Copy link

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

@rocksfrow
Copy link

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
Copy link

yogi-oc 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