This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one script to copy your Schema tables into another Schema of PostgreSQL.
I found this script at wiki.postgresql.org and modified the coding standard for the best use.
In our organization, I am creating a different schema for all the database developers, and at every new schema, we require to migrate development origin schema’s table data into a newly created schema.
I created different database users for each database developer, and they can only access their assigned schema. I am doing this for security reason, and it is also very easy to audit the changes and work progress of all the database developers.
Create a below function and execute to migrate your schema.
Create a function to copy old schema data to new schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
CREATE OR REPLACE FUNCTION fn_CopySchemaData(source_schema text, dest_schema text) RETURNS void AS $$ DECLARE object text; buffer text; default_ text; column_ text; BEGIN -- Create a new schema EXECUTE 'CREATE SCHEMA ' || dest_schema; 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; FOR object IN SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema LOOP buffer := dest_schema || '.' || object; EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING 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; END LOOP; END; $$ LANGUAGE plpgsql VOLATILE; |
Sample Execution:
|
1 |
select *from fn_CopySchemaData('old_schema','new_schema'); |
