Clone schema

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(New page: {{SnippetInfo|Aggregate Median|version=8.4|lang=SQL}} Category:SQL by Emanuel '3manuek' Just remember to create language. <source lang="sql"> CREATE OR REPLACE FUNCTION clone_schema(...)
 
Line 1: Line 1:
{{SnippetInfo|Aggregate Median|version=8.4|lang=SQL}}
+
{{SnippetInfo|Clone Schema|version=8.3|lang=SQL}}
 
[[Category:SQL]]
 
[[Category:SQL]]
 
by Emanuel '3manuek'
 
by Emanuel '3manuek'

Revision as of 19:00, 22 April 2010

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');
Personal tools