· 6 years ago · Aug 24, 2019, 09:46 PM
1-- Function: clone_schema(text, text)
2
3-- DROP FUNCTION clone_schema(text, text);
4
5CREATE OR REPLACE FUNCTION clone_schema(
6 source_schema text,
7 dest_schema text,
8 include_recs boolean)
9 RETURNS void AS
10$BODY$
11
12-- This function will clone all sequences, tables, data, views & functions from any existing schema to a new one
13-- SAMPLE CALL:
14-- SELECT clone_schema('public', 'new_schema', TRUE);
15
16DECLARE
17 src_oid oid;
18 tbl_oid oid;
19 func_oid oid;
20 object text;
21 buffer text;
22 srctbl text;
23 default_ text;
24 column_ text;
25 qry text;
26 dest_qry text;
27 v_def text;
28 seqval bigint;
29 sq_last_value bigint;
30 sq_max_value bigint;
31 sq_start_value bigint;
32 sq_increment_by bigint;
33 sq_min_value bigint;
34 sq_cache_value bigint;
35 sq_log_cnt bigint;
36 sq_is_called boolean;
37 sq_is_cycled boolean;
38 sq_cycled char(10);
39
40BEGIN
41
42-- Check that source_schema exists
43 SELECT oid INTO src_oid
44 FROM pg_namespace
45 WHERE nspname = quote_ident(source_schema);
46 IF NOT FOUND
47 THEN
48 RAISE NOTICE 'source schema % does not exist!', source_schema;
49 RETURN ;
50 END IF;
51
52 -- Check that dest_schema does not yet exist
53 PERFORM nspname
54 FROM pg_namespace
55 WHERE nspname = quote_ident(dest_schema);
56 IF FOUND
57 THEN
58 RAISE NOTICE 'dest schema % already exists!', dest_schema;
59 RETURN ;
60 END IF;
61
62 EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
63
64 -- Create sequences
65 -- TODO: Find a way to make this sequence's owner is the correct table.
66 FOR object IN
67 SELECT sequence_name::text
68 FROM information_schema.sequences
69 WHERE sequence_schema = quote_ident(source_schema)
70 LOOP
71 EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
72 srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
73
74 EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called
75 FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'
76 INTO sq_last_value, sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, sq_is_called ;
77
78 IF sq_is_cycled
79 THEN
80 sq_cycled := 'CYCLE';
81 ELSE
82 sq_cycled := 'NO CYCLE';
83 END IF;
84
85 EXECUTE 'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
86 || ' INCREMENT BY ' || sq_increment_by
87 || ' MINVALUE ' || sq_min_value
88 || ' MAXVALUE ' || sq_max_value
89 || ' START WITH ' || sq_start_value
90 || ' RESTART ' || sq_min_value
91 || ' CACHE ' || sq_cache_value
92 || sq_cycled || ' ;' ;
93
94 buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
95 IF include_recs
96 THEN
97 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ;
98 ELSE
99 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ;
100 END IF;
101
102 END LOOP;
103
104-- Create tables
105 FOR object IN
106 SELECT TABLE_NAME::text
107 FROM information_schema.tables
108 WHERE table_schema = quote_ident(source_schema)
109 AND table_type = 'BASE TABLE'
110
111 LOOP
112 buffer := dest_schema || '.' || quote_ident(object);
113 EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object)
114 || ' INCLUDING ALL)';
115
116 IF include_recs
117 THEN
118 -- Insert records from source table
119 EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
120 END IF;
121
122 FOR column_, default_ IN
123 SELECT column_name::text,
124 REPLACE(column_default::text, source_schema, dest_schema)
125 FROM information_schema.COLUMNS
126 WHERE table_schema = dest_schema
127 AND TABLE_NAME = object
128 AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
129 LOOP
130 EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
131 END LOOP;
132
133 END LOOP;
134
135-- add FK constraint
136 FOR qry IN
137 SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname)
138 || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || pg_get_constraintdef(ct.oid) || ';'
139 FROM pg_constraint ct
140 JOIN pg_class rn ON rn.oid = ct.conrelid
141 WHERE connamespace = src_oid
142 AND rn.relkind = 'r'
143 AND ct.contype = 'f'
144
145 LOOP
146 EXECUTE qry;
147
148 END LOOP;
149
150
151-- Create views
152 FOR object IN
153 SELECT table_name::text,
154 view_definition
155 FROM information_schema.views
156 WHERE table_schema = quote_ident(source_schema)
157
158 LOOP
159 buffer := dest_schema || '.' || quote_ident(object);
160 SELECT view_definition INTO v_def
161 FROM information_schema.views
162 WHERE table_schema = quote_ident(source_schema)
163 AND table_name = quote_ident(object);
164
165 EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
166
167 END LOOP;
168
169-- Create functions
170 FOR func_oid IN
171 SELECT oid
172 FROM pg_proc
173 WHERE pronamespace = src_oid
174
175 LOOP
176 SELECT pg_get_functiondef(func_oid) INTO qry;
177 SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;
178 EXECUTE dest_qry;
179
180 END LOOP;
181
182 RETURN;
183
184END;
185
186$BODY$
187 LANGUAGE plpgsql VOLATILE
188 COST 100;
189ALTER FUNCTION clone_schema(text, text, boolean)
190 OWNER TO postgres;