Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
enhanced postgresql clone_schema() function with trigger + foreign key support
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

This comment has been minimized.

Copy link
Owner Author

@rocksfrow rocksfrow commented 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

This comment has been minimized.

Copy link

@rosin1408 rosin1408 commented Mar 28, 2016

tks, this works well for me

@yogi-oc

This comment has been minimized.

Copy link

@yogi-oc yogi-oc commented Feb 17, 2017

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;

@ShepelievD

This comment has been minimized.

Copy link

@ShepelievD ShepelievD commented Dec 25, 2018

thanks @rocksfrow for hard work :)

One thing that I noticed should be changed:

 EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;

to

 EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT "'|| constraint_name_ ||'" '|| constraint_def_;

I put " around constraint_name_

Why? because postgres makes constraint_name_ to lower case, in case u have foreign key like that FK_foo_BAR_id postgres will copy that as fk_foo_bar_id

@Abenezer

This comment has been minimized.

Copy link

@Abenezer Abenezer commented Feb 5, 2019

case sensitive relationship names are not maintained ... for example i have a table User and User_Role .. the script try user_role and user with result in error relation not found.

@M-tower

This comment has been minimized.

Copy link

@M-tower M-tower commented May 5, 2020

Thanks! It helps me after on day of Google search.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment