· 8 years ago · Jul 02, 2018, 07:04 AM
1-- FUNCTION: public.clone_schema(text, text, boolean, boolean)
2
3-- DROP FUNCTION public.clone_schema(text, text, boolean, boolean);
4
5CREATE OR REPLACE FUNCTION public.clone_schema(
6 source_schema text,
7 dest_schema text,
8 include_recs boolean DEFAULT true,
9 show_details boolean DEFAULT false)
10 RETURNS void
11 LANGUAGE 'plpgsql'
12
13 COST 100
14 VOLATILE
15AS $BODY$
16
17-- This function will clone all sequences, tables, data, views & functions from any existing schema to a new one
18-- SAMPLE CALL:
19-- SELECT clone_schema('public', 'new_schema');
20-- SELECT clone_schema('public', 'new_schema', TRUE);
21-- SELECT clone_schema('public', 'new_schema', TRUE, TRUE);
22
23DECLARE
24 src_oid oid;
25 tbl_oid oid;
26 func_oid oid;
27 object text;
28 buffer text;
29 srctbl text;
30 default_ text;
31 column_ text;
32 qry text;
33 xrec record;
34 dest_qry text;
35 v_def text;
36 seqval bigint;
37 sq_last_value bigint;
38 sq_max_value bigint;
39 sq_start_value bigint;
40 sq_increment_by bigint;
41 sq_min_value bigint;
42 sq_cache_value bigint;
43 sq_log_cnt bigint;
44 sq_is_called boolean;
45 sq_is_cycled boolean;
46 sq_cycled char(10);
47 rec record;
48 source_schema_dot text = source_schema || '.';
49 dest_schema_dot text = dest_schema || '.';
50
51BEGIN
52
53 -- Check that source_schema exists
54 SELECT oid INTO src_oid
55 FROM pg_namespace
56 WHERE nspname = quote_ident(source_schema);
57 IF NOT FOUND
58 THEN
59 RAISE NOTICE 'source schema % does not exist!', source_schema;
60 RETURN ;
61 END IF;
62
63 -- Check that dest_schema does not yet exist
64 PERFORM nspname
65 FROM pg_namespace
66 WHERE nspname = quote_ident(dest_schema);
67 IF FOUND
68 THEN
69 RAISE NOTICE 'dest schema % already exists!', dest_schema;
70 RETURN ;
71 END IF;
72
73 EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
74
75 -- Defaults search_path to destination schema
76 PERFORM set_config('search_path', dest_schema, true);
77
78 -- Create sequences
79 -- TODO: Find a way to make this sequence's owner is the correct table.
80 FOR object IN
81 SELECT sequence_name::text
82 FROM information_schema.sequences
83 WHERE sequence_schema = quote_ident(source_schema)
84 LOOP
85 EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
86 srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
87
88 EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called
89 FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'
90 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 ;
91
92 IF sq_is_cycled
93 THEN
94 sq_cycled := 'CYCLE';
95 ELSE
96 sq_cycled := 'NO CYCLE';
97 END IF;
98
99 EXECUTE 'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
100 || ' INCREMENT BY ' || sq_increment_by
101 || ' MINVALUE ' || sq_min_value
102 || ' MAXVALUE ' || sq_max_value
103 || ' START WITH ' || sq_start_value
104 || ' RESTART ' || sq_min_value
105 || ' CACHE ' || sq_cache_value
106 || sq_cycled || ' ;' ;
107
108 buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
109 IF include_recs
110 THEN
111 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ;
112 ELSE
113 EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ;
114 END IF;
115 IF show_details THEN RAISE NOTICE 'Sequence created: %', object; END IF;
116 END LOOP;
117
118 -- Create tables
119 FOR object IN
120 SELECT TABLE_NAME::text
121 FROM information_schema.tables
122 WHERE table_schema = quote_ident(source_schema)
123 AND table_type = 'BASE TABLE'
124
125 LOOP
126 buffer := dest_schema || '.' || quote_ident(object);
127 EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object)
128 || ' INCLUDING ALL)';
129
130 IF include_recs
131 THEN
132 -- Insert records from source table
133 EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
134 END IF;
135
136 FOR column_, default_ IN
137 SELECT column_name::text,
138 REPLACE(column_default::text, source_schema, dest_schema)
139 FROM information_schema.COLUMNS
140 WHERE table_schema = dest_schema
141 AND TABLE_NAME = object
142 AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
143 LOOP
144 EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
145 END LOOP;
146
147 IF show_details THEN RAISE NOTICE 'base table created: %', object; END IF;
148
149 END LOOP;
150
151 -- add FK constraint
152 FOR xrec IN
153 SELECT ct.conname as fk_name, rn.relname as tb_name, 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname)
154 || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), source_schema_dot, '') || ';' as qry
155 FROM pg_constraint ct
156 JOIN pg_class rn ON rn.oid = ct.conrelid
157 WHERE connamespace = src_oid
158 AND rn.relkind = 'r'
159 AND ct.contype = 'f'
160
161 LOOP
162 IF show_details THEN RAISE NOTICE 'Creating FK constraint %.%...', xrec.tb_name, xrec.fk_name; END IF;
163 --RAISE NOTICE 'DEF: %', xrec.qry;
164 EXECUTE xrec.qry;
165 END LOOP;
166
167 -- Create functions
168 -- FOR xrec IN
169 -- SELECT proname as func_name, oid as func_oid
170 -- FROM pg_proc
171 -- WHERE pronamespace = src_oid
172 --
173 -- LOOP
174 -- IF show_details THEN RAISE NOTICE 'Creating function %...', xrec.func_name; END IF;
175 -- SELECT pg_get_functiondef(xrec.func_oid) INTO qry;
176 -- SELECT replace(qry, source_schema_dot, '') INTO dest_qry;
177 -- EXECUTE dest_qry;
178 -- END LOOP;
179
180 -- add Table Triggers
181 FOR rec IN
182 SELECT
183 trg.tgname AS trigger_name,
184 tbl.relname AS trigger_table,
185
186 CASE
187 WHEN trg.tgenabled='O' THEN 'ENABLED'
188 ELSE 'DISABLED'
189 END AS status,
190 CASE trg.tgtype::integer & 1
191 WHEN 1 THEN 'ROW'::text
192 ELSE 'STATEMENT'::text
193 END AS trigger_level,
194 CASE trg.tgtype::integer & 66
195 WHEN 2 THEN 'BEFORE'
196 WHEN 64 THEN 'INSTEAD OF'
197 ELSE 'AFTER'
198 END AS action_timing,
199 CASE trg.tgtype::integer & cast(60 AS int2)
200 WHEN 16 THEN 'UPDATE'
201 WHEN 8 THEN 'DELETE'
202 WHEN 4 THEN 'INSERT'
203 WHEN 20 THEN 'INSERT OR UPDATE'
204 WHEN 28 THEN 'INSERT OR UPDATE OR DELETE'
205 WHEN 24 THEN 'UPDATE OR DELETE'
206 WHEN 12 THEN 'INSERT OR DELETE'
207 WHEN 32 THEN 'TRUNCATE'
208 END AS trigger_event,
209 'EXECUTE PROCEDURE ' || (SELECT nspname FROM pg_namespace where oid = pc.pronamespace )
210 || '.' || proname || '('
211 || regexp_replace(replace(trim(trailing '\000' from encode(tgargs,'escape')), '\000',','),'{(.+)}','''{\1}''','g')
212 || ')' as action_statement
213
214 FROM pg_trigger trg
215 JOIN pg_class tbl on trg.tgrelid = tbl.oid
216 JOIN pg_proc pc ON pc.oid = trg.tgfoid
217 WHERE trg.tgname not like 'RI_ConstraintTrigger%'
218 AND trg.tgname not like 'pg_sync_pg%'
219 AND tbl.relnamespace = (SELECT oid FROM pg_namespace where nspname = quote_ident(source_schema) )
220
221 LOOP
222 buffer := dest_schema || '.' || quote_ident(rec.trigger_table);
223 IF show_details THEN RAISE NOTICE 'Creating trigger % % % ON %...', rec.trigger_name, rec.action_timing, rec.trigger_event, rec.trigger_table; END IF;
224 EXECUTE 'CREATE TRIGGER ' || rec.trigger_name || ' ' || rec.action_timing
225 || ' ' || rec.trigger_event || ' ON ' || buffer || ' FOR EACH '
226 || rec.trigger_level || ' ' || replace(rec.action_statement, source_schema_dot, '');
227
228 END LOOP;
229
230 -- Create views
231 FOR object IN
232 SELECT table_name::text,
233 view_definition
234 FROM information_schema.views
235 WHERE table_schema = quote_ident(source_schema)
236
237 LOOP
238 buffer := dest_schema || '.' || quote_ident(object);
239 SELECT replace(view_definition, source_schema_dot, '') INTO v_def
240 FROM information_schema.views
241 WHERE table_schema = quote_ident(source_schema)
242 AND table_name = quote_ident(object);
243 IF show_details THEN RAISE NOTICE 'Creating view % AS %', object, regexp_replace(v_def, '[\n\r]+', ' ', 'g'); END IF;
244 EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
245
246 END LOOP;
247
248 RETURN;
249
250END;
251
252$BODY$;
253
254ALTER FUNCTION public.clone_schema(text, text, boolean, boolean)
255 OWNER TO production;