7fdLgL7Z

1-- Function: clone_schema(text, text, include_recs)
2
3CREATE OR REPLACE FUNCTION clone_schema(
4    source_schema text,
5    dest_schema text,
6    include_recs boolean)
7  RETURNS void AS
8$BODY$
9
10--  This function will clone all tables, data, views & functions from any existing schema to a new one
11-- SAMPLE CALL:
12-- SELECT clone_schema('public', 'new_schema', TRUE);
13
14DECLARE
15  src_oid          oid;
16  tbl_oid          oid;
17  func_oid         oid;
18  object           text;
19  buffer           text;
20  srctbl           text;
21  default_         text;
22  column_          text;
23  qry              text;
24  dest_qry         text;
25  v_def            text;
26
27BEGIN
28
29-- Check that source_schema exists
30  SELECT oid INTO src_oid
31    FROM pg_namespace
32   WHERE nspname = quote_ident(source_schema);
33  IF NOT FOUND
34    THEN 
35    RAISE NOTICE 'source schema % does not exist!', source_schema;
36    RETURN ;
37  END IF;
38
39  -- Check that dest_schema does not yet exist
40  PERFORM nspname 
41    FROM pg_namespace
42   WHERE nspname = quote_ident(dest_schema);
43  IF FOUND
44    THEN 
45    RAISE NOTICE 'dest schema % already exists!', dest_schema;
46    RETURN ;
47  END IF;
48
49  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
50
51-- Create tables 
52  FOR object IN
53    SELECT TABLE_NAME::text 
54      FROM information_schema.tables 
55     WHERE table_schema = quote_ident(source_schema)
56       AND table_type = 'BASE TABLE'
57
58  LOOP
59    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
60    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) 
61        || ' INCLUDING ALL)';
62
63    IF include_recs 
64      THEN 
65      -- Insert records from source table
66      EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
67    END IF;
68 
69    FOR column_, default_ IN
70      SELECT column_name::text, 
71             REPLACE(column_default::text, source_schema, dest_schema) 
72        FROM information_schema.COLUMNS 
73       WHERE table_schema = quote_ident(dest_schema) 
74         AND TABLE_NAME = object 
75         AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
76    LOOP
77      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
78    END LOOP;
79
80  END LOOP;
81
82--  add FK constraint
83  FOR qry IN
84    SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) 
85                          || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema), quote_ident(dest_schema)) || ';'
86      FROM pg_constraint ct
87      JOIN pg_class rn ON rn.oid = ct.conrelid
88     WHERE connamespace = src_oid
89       AND rn.relkind = 'r'
90       AND ct.contype = 'f'
91         
92    LOOP
93      EXECUTE qry;
94
95    END LOOP;
96
97
98-- Create views 
99  FOR object IN
100    SELECT table_name::text,
101           view_definition 
102      FROM information_schema.views
103     WHERE table_schema = quote_ident(source_schema)
104
105  LOOP
106    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
107    SELECT view_definition INTO v_def
108      FROM information_schema.views
109     WHERE table_schema = quote_ident(source_schema)
110       AND table_name = quote_ident(object);
111     
112    EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
113
114  END LOOP;
115
116-- Create functions 
117  FOR func_oid IN
118    SELECT oid
119      FROM pg_proc 
120     WHERE pronamespace = src_oid
121
122  LOOP      
123    SELECT pg_get_functiondef(func_oid) INTO qry;
124    SELECT replace(qry, quote_ident(source_schema), quote_ident(dest_schema)) INTO dest_qry;
125    EXECUTE dest_qry;
126
127  END LOOP;
128  
129  RETURN; 
130 
131END;
132 
133$BODY$
134  LANGUAGE plpgsql VOLATILE
135  COST 100;
136