Clone schema

From PostgreSQL wiki

Revision as of 12:11, 6 April 2013 by Schinckel (Talk | contribs)

Jump to: navigation, search

Snippets

Clone Schema

Works with PostgreSQL

8.3

Written in

SQL

Depends on

Nothing

by Emanuel '3manuek'

Just remember to create language.

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$BODY$
DECLARE 
  objeto text;
  buffer text;
BEGIN
    EXECUTE 'CREATE SCHEMA ' || dest_schema ;
 
 
    FOR objeto IN
        SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
    LOOP        
        buffer := dest_schema || '.' || objeto;
        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || objeto || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
        EXECUTE 'INSERT INTO ' || buffer || '(SELECT * FROM ' || source_schema || '.' || objeto || ')';
    END LOOP;
 
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Execution is simple:

SELECT clone_schema('old_schema','new_schema');


Worth noting is that this copies everything in the schema, including the default values. Which, in the case of serial fields, will mean the default value points to the old schema:


   nextval('old_schema.sequence_name'::regclass)


This actually has some nice side-effects: the original sequence is used for creating new ids, which means you have unique ids across tables.

However, very bad things will happen if you were to remove the source schema, as the sequence would also be removed.

Personal tools