· 8 years ago · Mar 22, 2018, 06:34 AM
1-- THIS SCRIPT HAS NOT BEEN TESTED, PLEASE CHECK CAREFULLY BEFORE RUNNING IT
2-- USE AT YOUR OWN RISK
3
4-- Function: public.clone_schema(text, text, boolean)
5
6-- DROP FUNCTION public.clone_schema(text, text, boolean);
7
8CREATE OR REPLACE FUNCTION public.clone_schema(
9 source_schema text,
10 dest_schema text,
11 include_recs boolean)
12 RETURNS void AS
13$BODY$
14-- * Initial code by Emanuel '3manuek'
15-- * Revision 2017-04-17 by Melvin Davidson
16-- Added SELECT REPLACE for schema views
17-- * Revision 2018-03-13 by Aldrin Martoq
18-- PostgreSQL 10 sequences support
19--
20-- This function will clone all sequences, tables, indexes, rules, triggers,
21-- data(optional), views & functions from any existing schema to a new schema
22-- SAMPLE CALL:
23-- SELECT clone_schema('public', 'new_schema', TRUE);
24
25DECLARE
26 src_oid oid;
27 tbl_oid oid;
28 func_oid oid;
29 con_oid oid;
30 v_path text;
31 v_func text;
32 v_args text;
33 v_conname text;
34 v_rule text;
35 v_trig text;
36 object text;
37 buffer text;
38 srctbl text;
39 default_ text;
40 v_column text;
41 qry text;
42 dest_qry text;
43 v_def text;
44 v_stat integer;
45 seqval bigint;
46 sq_last_value bigint;
47 sq_max_value bigint;
48 sq_start_value bigint;
49 sq_increment_by bigint;
50 sq_min_value bigint;
51 sq_cache_value bigint;
52 sq_log_cnt bigint;
53 sq_is_called boolean;
54 sq_is_cycled boolean;
55 sq_cycled char(10);
56 seq_cataloged boolean;
57
58BEGIN
59
60 -- Check that source_schema exists
61 SELECT oid INTO src_oid
62 FROM pg_namespace
63 WHERE nspname = quote_ident(source_schema);
64 IF NOT FOUND
65 THEN
66 RAISE NOTICE 'source schema % does not exist!', source_schema;
67 RETURN;
68 END IF;
69
70 -- Check that dest_schema does not yet exist
71 PERFORM nspname
72 FROM pg_namespace
73 WHERE nspname = quote_ident(dest_schema);
74 IF FOUND
75 THEN
76 RAISE NOTICE 'dest schema % already exists!', dest_schema;
77 RETURN;
78 END IF;
79
80 EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
81
82 -- Add schema comment
83 SELECT description INTO v_def
84 FROM pg_description
85 WHERE objoid = src_oid
86 AND objsubid = 0;
87 IF FOUND
88 THEN
89 EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || quote_literal(v_def);
90 END IF;
91
92 -- Check for pg_sequences system view in PostgreSQL 10
93 SELECT EXISTS INTO seq_cataloged (
94 SELECT 1
95 FROM pg_catalog.pg_views
96 WHERE schemaname = 'pg_catalog'
97 AND viewname = 'pg_sequences'
98 );
99
100 -- Create sequences
101 -- TODO: Find a way to make this sequence's owner is the correct table.
102 FOR object IN
103 SELECT sequence_name::text
104 FROM information_schema.sequences
105 WHERE sequence_schema = quote_ident(source_schema)
106 LOOP
107 EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
108 srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
109
110 IF seq_cataloged THEN
111 SELECT max_value, start_value, increment_by, min_value, cache_size AS cache_value, cycle AS is_cycled
112 INTO sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_is_cycled
113 FROM pg_catalog.pg_sequences
114 WHERE schemaname = quote_ident(source_schema)
115 AND sequencename = quote_ident(object);
116
117 EXECUTE 'SELECT last_value, log_cnt, is_called
118 FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'
119 INTO sq_last_value, sq_log_cnt, sq_is_called;
120 ELSE
121 EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called
122 FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'
123 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;
124 END IF;
125
126
127 IF sq_is_cycled
128 THEN
129 sq_cycled := 'CYCLE';
130 ELSE
131 sq_cycled := 'NO CYCLE';
132 END IF;
133
134 EXECUTE 'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
135 || ' INCREMENT BY ' || sq_increment_by
136 || ' MINVALUE ' || sq_min_value
137 || ' MAXVALUE ' || sq_max_value
138 || ' START WITH ' || sq_start_value
139 || ' RESTART ' || sq_min_value
140 || ' CACHE ' || sq_cache_value
141 || sq_cycled || ' ;' ;
142
143 buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
144 IF include_recs
145 THEN
146 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ;
147 ELSE
148 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ;
149 END IF;
150
151 -- add sequence comments
152 SELECT oid INTO tbl_oid
153 FROM pg_class
154 WHERE relkind = 'S'
155 AND relnamespace = src_oid
156 AND relname = quote_ident(object);
157
158 SELECT description INTO v_def
159 FROM pg_description
160 WHERE objoid = tbl_oid
161 AND objsubid = 0;
162
163 IF FOUND
164 THEN
165 EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
166 || ' IS ''' || v_def || ''';';
167 END IF;
168
169
170 END LOOP;
171
172-- Create tables
173 FOR object IN
174 SELECT TABLE_NAME::text
175 FROM information_schema.tables
176 WHERE table_schema = quote_ident(source_schema)
177 AND table_type = 'BASE TABLE'
178
179 LOOP
180 buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
181 EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object)
182 || ' INCLUDING ALL)';
183
184 -- Add table comment
185 SELECT oid INTO tbl_oid
186 FROM pg_class
187 WHERE relkind = 'r'
188 AND relnamespace = src_oid
189 AND relname = quote_ident(object);
190
191 SELECT description INTO v_def
192 FROM pg_description
193 WHERE objoid = tbl_oid
194 AND objsubid = 0;
195
196 IF FOUND
197 THEN
198 EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
199 || ' IS ''' || v_def || ''';';
200 END IF;
201
202 IF include_recs
203 THEN
204 -- Insert records from source table
205 EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
206 END IF;
207
208 FOR v_column, default_ IN
209 SELECT column_name::text,
210 REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' )
211 FROM information_schema.COLUMNS
212 WHERE table_schema = dest_schema
213 AND TABLE_NAME = object
214 AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
215 LOOP
216 EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET DEFAULT ' || default_;
217
218 END LOOP;
219
220 END LOOP;
221
222 -- set column statistics
223 FOR tbl_oid, srctbl IN
224 SELECT oid, relname
225 FROM pg_class
226 WHERE relnamespace = src_oid
227 AND relkind = 'r'
228
229 LOOP
230
231 FOR v_column, v_stat IN
232 SELECT attname, attstattarget
233 FROM pg_attribute
234 WHERE attrelid = tbl_oid
235 AND attnum > 0
236
237 LOOP
238
239 buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl);
240-- RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %', buffer, v_column, v_stat::text;
241 EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';';
242
243 END LOOP;
244 END LOOP;
245
246-- add FK constraint
247 FOR qry IN
248 SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname)
249 || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') || ';'
250 FROM pg_constraint ct
251 JOIN pg_class rn ON rn.oid = ct.conrelid
252 WHERE connamespace = src_oid
253 AND rn.relkind = 'r'
254 AND ct.contype = 'f'
255
256 LOOP
257 EXECUTE qry;
258
259 END LOOP;
260
261 -- Add constraint comment
262 FOR con_oid IN
263 SELECT oid
264 FROM pg_constraint
265 WHERE conrelid = tbl_oid
266
267 LOOP
268 SELECT conname INTO v_conname
269 FROM pg_constraint
270 WHERE oid = con_oid;
271
272 SELECT description INTO v_def
273 FROM pg_description
274 WHERE objoid = con_oid;
275
276 IF FOUND
277 THEN
278 EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' || quote_ident(dest_schema) || '.' || quote_ident(object)
279 || ' IS ''' || v_def || ''';';
280 END IF;
281
282 END LOOP;
283
284
285-- Create views
286 FOR object IN
287 SELECT table_name::text,
288 view_definition
289 FROM information_schema.views
290 WHERE table_schema = quote_ident(source_schema)
291
292 LOOP
293 buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
294 SELECT view_definition INTO v_def
295 FROM information_schema.views
296 WHERE table_schema = quote_ident(source_schema)
297 AND table_name = quote_ident(object);
298
299 SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def;
300-- RAISE NOTICE 'view def, % , source % , dest % ', v_def, source_schema, dest_schema;
301
302 EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
303
304 -- Add comment
305 SELECT oid INTO tbl_oid
306 FROM pg_class
307 WHERE relkind = 'v'
308 AND relnamespace = src_oid
309 AND relname = quote_ident(object);
310
311 SELECT description INTO v_def
312 FROM pg_description
313 WHERE objoid = tbl_oid
314 AND objsubid = 0;
315
316 IF FOUND
317 THEN
318 EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(object)
319 || ' IS ' || quote_literal(v_def);
320 END IF;
321
322
323 END LOOP;
324
325-- Create functions
326 FOR func_oid IN
327 SELECT oid, proargnames
328 FROM pg_proc
329 WHERE pronamespace = src_oid
330
331 LOOP
332 SELECT pg_get_functiondef(func_oid) INTO qry;
333 SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args
334 FROM pg_proc
335 WHERE oid = func_oid;
336 SELECT replace(qry, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') INTO dest_qry;
337 EXECUTE dest_qry;
338
339 -- Add function comment
340 SELECT description INTO v_def
341 FROM pg_description
342 WHERE objoid = func_oid
343 AND objsubid = 0;
344
345 IF FOUND
346 THEN
347-- RAISE NOTICE 'func_oid %, object %, v_args %', func_oid::text, quote_ident(object), v_args;
348 EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' || quote_ident(v_func) || '(' || v_args || ')'
349 || ' IS ' || quote_literal(v_def) ||';' ;
350 END IF;
351
352
353 END LOOP;
354
355 -- add Rules
356 FOR v_def IN
357 SELECT definition
358 FROM pg_rules
359 WHERE schemaname = quote_ident(source_schema)
360
361 LOOP
362
363 IF v_def IS NOT NULL
364 THEN
365 SELECT replace(v_def, 'TO ', 'TO ' || quote_ident(dest_schema) || '.') INTO v_def;
366 EXECUTE ' ' || v_def;
367 END IF;
368 END LOOP;
369
370 -- add triggers
371 FOR v_def IN
372 SELECT pg_get_triggerdef(oid)
373 FROM pg_trigger
374 WHERE tgname NOT LIKE 'RI_%'
375 AND tgrelid IN (SELECT oid
376 FROM pg_class
377 WHERE relkind = 'r'
378 AND relnamespace = src_oid)
379
380 LOOP
381
382 SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || '.') INTO dest_qry;
383 EXECUTE dest_qry;
384
385 END LOOP;
386 -- Disable inactive triggers
387 -- D = disabled
388 FOR tbl_oid IN
389 SELECT oid
390 FROM pg_trigger
391 WHERE tgenabled = 'D'
392 AND tgname NOT LIKE 'RI_%'
393 AND tgrelid IN (SELECT oid
394 FROM pg_class
395 WHERE relkind = 'r'
396 AND relnamespace = src_oid)
397 LOOP
398 SELECT t.tgname, c.relname INTO object, srctbl
399 FROM pg_trigger t
400 JOIN pg_class c ON c.oid = t.tgrelid
401 WHERE t.oid = tbl_oid;
402
403 IF FOUND
404 THEN
405 EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE TRIGGER ' || object || ';';
406 END IF;
407
408 END LOOP;
409
410 -- Add index comment
411
412 FOR tbl_oid IN
413 SELECT oid
414 FROM pg_class
415 WHERE relkind = 'i'
416 AND relnamespace = src_oid
417
418 LOOP
419
420 SELECT relname INTO object
421 FROM pg_class
422 WHERE oid = tbl_oid;
423 SELECT description INTO v_def
424 FROM pg_description
425 WHERE objoid = tbl_oid
426 AND objsubid = 0;
427
428 IF FOUND
429 THEN
430 EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || quote_ident(object)
431 || ' IS ''' || v_def || ''';';
432 END IF;
433
434 END LOOP;
435
436 -- add rule comments
437 FOR con_oid IN
438 SELECT oid, *
439 FROM pg_rewrite
440 WHERE rulename <> '_RETURN'::name
441
442 LOOP
443
444 SELECT rulename, ev_class INTO v_rule, tbl_oid
445 FROM pg_rewrite
446 WHERE oid = con_oid;
447
448 SELECT relname INTO object
449 FROM pg_class
450 WHERE oid = tbl_oid
451 AND relkind = 'r';
452
453 SELECT description INTO v_def
454 FROM pg_description
455 WHERE objoid = con_oid
456 AND objsubid = 0;
457
458 IF FOUND
459 THEN
460 EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
461 END IF;
462
463 END LOOP;
464
465 -- add trigger comments
466 FOR con_oid IN
467 SELECT oid, *
468 FROM pg_trigger
469 WHERE tgname NOT LIKE 'RI_%'
470
471 LOOP
472
473 SELECT tgname, tgrelid INTO v_trig, tbl_oid
474 FROM pg_trigger
475 WHERE oid = con_oid;
476
477 SELECT relname INTO object
478 FROM pg_class
479 WHERE oid = tbl_oid
480 AND relkind = 'r';
481
482 SELECT description INTO v_def
483 FROM pg_description
484 WHERE objoid = con_oid
485 AND objsubid = 0;
486
487 IF FOUND
488 THEN
489 EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
490 END IF;
491
492 END LOOP;
493
494 RETURN;
495
496
497END;
498
499$BODY$
500 LANGUAGE plpgsql VOLATILE
501 COST 100;
502ALTER FUNCTION public.clone_schema(text, text, boolean)
503 OWNER TO postgres;
504COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates sequences, tables, indexes, rules, triggers, data(optional),
505 views & functions from the source schema to the destination schema';