On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta <diascosta@xxxxxxxxxxxxx> wrote:
Hi Melvin,
I'm new to clone_schema.
Can I use it on PostgreSQL 9.6?
TIA
DCostaployment by invitation only!
>
Can I use it on PostgreSQL 9.6?
Yes, but because the developer(s) once again monkeyed with the system catalogs, there are now
two versions. One for 10 and one for 9.6 and below. I've attached the 9.6 version for you.
-- Function: public.clone_schema(text, text, boolean) -- DROP FUNCTION public.clone_schema(text, text, boolean); CREATE OR REPLACE FUNCTION public.clone_schema( source_schema text, dest_schema text, include_recs boolean) RETURNS void AS $BODY$ -- Initial code by Emanuel '3manuek' -- Last revision 2017-04-17 by Melvin Davidson -- Added SELECT REPLACE for schema views -- -- This function will clone all sequences, tables, indexes, rules, triggers, -- data(optional), views & functions from any existing schema to a new schema -- SAMPLE CALL: -- SELECT clone_schema('public', 'new_schema', TRUE); DECLARE src_oid oid; tbl_oid oid; func_oid oid; con_oid oid; v_path text; v_func text; v_args text; v_conname text; v_rule text; v_trig text; object text; buffer text; srctbl text; default_ text; v_column text; qry text; dest_qry text; v_def text; v_stat integer; seqval bigint; sq_last_value bigint; sq_max_value bigint; sq_start_value bigint; sq_increment_by bigint; sq_min_value bigint; sq_cache_value bigint; sq_log_cnt bigint; sq_is_called boolean; sq_is_cycled boolean; sq_cycled char(10); BEGIN -- Check that source_schema exists SELECT oid INTO src_oid FROM pg_namespace WHERE nspname = quote_ident(source_schema); IF NOT FOUND THEN RAISE NOTICE 'source schema % does not exist!', source_schema; RETURN ; END IF; -- Check that dest_schema does not yet exist PERFORM nspname FROM pg_namespace WHERE nspname = quote_ident(dest_schema); IF FOUND THEN RAISE NOTICE 'dest schema % already exists!', dest_schema; RETURN ; END IF; EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ; -- Add schema comment SELECT description INTO v_def FROM pg_description WHERE objoid = src_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || quote_literal(v_def); END IF; SET search_path TO dest_schema, source_schema, 'public', pg_catalog; SELECT current_schemas(TRUE) INTO v_path; RAISE NOTICE 'search path = %', v_path; -- Create sequences -- TODO: Find a way to make this sequence's owner is the correct table. FOR object IN SELECT sequence_name::text FROM information_schema.sequences WHERE sequence_schema = quote_ident(source_schema) LOOP EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object); srctbl := quote_ident(source_schema) || '.' || quote_ident(object); EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';' 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 ; IF sq_is_cycled THEN sq_cycled := 'CYCLE'; ELSE sq_cycled := 'NO CYCLE'; END IF; EXECUTE 'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' INCREMENT BY ' || sq_increment_by || ' MINVALUE ' || sq_min_value || ' MAXVALUE ' || sq_max_value || ' START WITH ' || sq_start_value || ' RESTART ' || sq_min_value || ' CACHE ' || sq_cache_value || sq_cycled || ' ;' ; buffer := quote_ident(dest_schema) || '.' || quote_ident(object); IF include_recs THEN EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ; ELSE EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ; END IF; -- add sequence comments SELECT oid INTO tbl_oid FROM pg_class WHERE relkind = 'S' AND relnamespace = src_oid AND relname = quote_ident(object); SELECT description INTO v_def FROM pg_description WHERE objoid = tbl_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' IS ''' || v_def || ''';'; END IF; END LOOP; -- Create tables FOR object IN SELECT TABLE_NAME::text FROM information_schema.tables WHERE table_schema = quote_ident(source_schema) AND table_type = 'BASE TABLE' LOOP buffer := quote_ident(dest_schema) || '.' || quote_ident(object); EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) || ' INCLUDING ALL)'; -- Add table comment SELECT oid INTO tbl_oid FROM pg_class WHERE relkind = 'r' AND relnamespace = src_oid AND relname = quote_ident(object); SELECT description INTO v_def FROM pg_description WHERE objoid = tbl_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' IS ''' || v_def || ''';'; END IF; IF include_recs THEN -- Insert records from source table EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'; END IF; FOR v_column, default_ IN SELECT column_name::text, REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' ) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)' LOOP EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET DEFAULT ' || default_; END LOOP; END LOOP; -- set column statistics FOR tbl_oid, srctbl IN SELECT oid, relname FROM pg_class WHERE relnamespace = src_oid AND relkind = 'r' LOOP FOR v_column, v_stat IN SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = tbl_oid AND attnum > 0 LOOP buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl); -- RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %', buffer, v_column, v_stat::text; EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';'; END LOOP; END LOOP; -- add FK constraint FOR qry IN SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') || ';' FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid WHERE connamespace = src_oid AND rn.relkind = 'r' AND ct.contype = 'f' LOOP EXECUTE qry; END LOOP; -- Add constraint comment FOR con_oid IN SELECT oid FROM pg_constraint WHERE conrelid = tbl_oid LOOP SELECT conname INTO v_conname FROM pg_constraint WHERE oid = con_oid; SELECT description INTO v_def FROM pg_description WHERE objoid = con_oid; IF FOUND THEN EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' IS ''' || v_def || ''';'; END IF; END LOOP; -- Create views FOR object IN SELECT table_name::text, view_definition FROM information_schema.views WHERE table_schema = quote_ident(source_schema) LOOP buffer := quote_ident(dest_schema) || '.' || quote_ident(object); SELECT view_definition INTO v_def FROM information_schema.views WHERE table_schema = quote_ident(source_schema) AND table_name = quote_ident(object); SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def; -- RAISE NOTICE 'view def, % , source % , dest % ', v_def, source_schema, dest_schema; EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ; -- Add comment SELECT oid INTO tbl_oid FROM pg_class WHERE relkind = 'v' AND relnamespace = src_oid AND relname = quote_ident(object); SELECT description INTO v_def FROM pg_description WHERE objoid = tbl_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' IS ' || quote_literal(v_def); END IF; END LOOP; -- Create functions FOR func_oid IN SELECT oid, proargnames FROM pg_proc WHERE pronamespace = src_oid LOOP SELECT pg_get_functiondef(func_oid) INTO qry; SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args FROM pg_proc WHERE oid = func_oid; SELECT replace(qry, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') INTO dest_qry; EXECUTE dest_qry; -- Add function comment SELECT description INTO v_def FROM pg_description WHERE objoid = func_oid AND objsubid = 0; IF FOUND THEN -- RAISE NOTICE 'func_oid %, object %, v_args %', func_oid::text, quote_ident(object), v_args; EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' || quote_ident(v_func) || '(' || v_args || ')' || ' IS ' || quote_literal(v_def) ||';' ; END IF; END LOOP; -- add Rules SET search_path TO dest_schema; FOR v_def IN SELECT definition FROM pg_rules WHERE schemaname = quote_ident(source_schema) LOOP IF v_def IS NOT NULL THEN SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO v_def; RAISE NOTICE 'new rule %', v_def; EXECUTE ' ' || v_def; END IF; END LOOP; -- add triggers FOR v_def IN SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname NOT LIKE 'RI_%' AND tgrelid IN (SELECT oid FROM pg_class WHERE relkind = 'r' AND relnamespace = src_oid) LOOP SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO dest_qry; EXECUTE dest_qry; END LOOP; -- Disable inactive triggers -- D = disabled FOR tbl_oid IN SELECT oid FROM pg_trigger WHERE tgenabled = 'D' AND tgname NOT LIKE 'RI_%' AND tgrelid IN (SELECT oid FROM pg_class WHERE relkind = 'r' AND relnamespace = src_oid) LOOP SELECT t.tgname, c.relname INTO object, srctbl FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid WHERE t.oid = tbl_oid; IF FOUND THEN EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE TRIGGER ' || object || ';'; END IF; END LOOP; -- Add index comment FOR tbl_oid IN SELECT oid FROM pg_class WHERE relkind = 'i' AND relnamespace = src_oid LOOP SELECT relname INTO object FROM pg_class WHERE oid = tbl_oid; SELECT description INTO v_def FROM pg_description WHERE objoid = tbl_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || quote_ident(object) || ' IS ''' || v_def || ''';'; END IF; END LOOP; -- add rule comments FOR con_oid IN SELECT oid, * FROM pg_rewrite WHERE rulename <> '_RETURN'::name LOOP SELECT rulename, ev_class INTO v_rule, tbl_oid FROM pg_rewrite WHERE oid = con_oid; SELECT relname INTO object FROM pg_class WHERE oid = tbl_oid AND relkind = 'r'; SELECT description INTO v_def FROM pg_description WHERE objoid = con_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def); END IF; END LOOP; -- add trigger comments FOR con_oid IN SELECT oid, * FROM pg_trigger WHERE tgname NOT LIKE 'RI_%' LOOP SELECT tgname, tgrelid INTO v_trig, tbl_oid FROM pg_trigger WHERE oid = con_oid; SELECT relname INTO object FROM pg_class WHERE oid = tbl_oid AND relkind = 'r'; SELECT description INTO v_def FROM pg_description WHERE objoid = con_oid AND objsubid = 0; IF FOUND THEN EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def); END IF; END LOOP; RETURN; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION public.clone_schema(text, text, boolean) OWNER TO postgres; COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates sequences, tables, indexes, rules, triggers, data(optional), views & functions from the source schema to the destination schema';