Skip to content

Instantly share code, notes, and snippets.

@retro
Forked from hielkehoeve/postgresql clone schema
Created January 9, 2017 17:43
Show Gist options
  • Save retro/7f8a431208f123150a2c45c3bbf47da5 to your computer and use it in GitHub Desktop.
Save retro/7f8a431208f123150a2c45c3bbf47da5 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment