I think you'd have better luck asking this on pgsql-general news list. https://www.postgresql.org/list/pgsql-general/
There is no PostGIS related question here. From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Olivier Leprêtre Sent: Sunday, July 30, 2017 2:24 PM To: postgis-users@lists.osgeo.org Subject: [postgis-users] clone schema - pg_get_serial_sequence returns null Hi, I want to clone a schema, so I wrote the script below. My problem is that I cant get sequences with pg_get_serial_sequence for the cloned primary keys. The sequence seems to be ok in the cloned schema : idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_seq'::regclass), CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp) But if I run select pg_get_serial_sequence('pr_1_1.ptbt', 'idapp') it returns <NULL> Conversely, running pg_get_serial_sequence('source.ptbt', 'idapp') (from the source schema) returns the correct sequence. Im using 9.6, I saw that it could be a problem because column is not directly as serial, is this an answer without solution ? Any help will be appreciated ! Thanks, Olivier CREATE OR REPLACE FUNCTION params.prm_colne_schema(source_schema text, dest_schema text) RETURNS boolean AS $BODY$ DECLARE object text; buffer text; default_ text; column_ text; sqls text; BEGIN IF dest_schema <> 'public' THEN EXECUTE 'DROP SCHEMA if exists ' || dest_schema || ' CASCADE'; EXECUTE 'CREATE SCHEMA ' || dest_schema; END IF; -- copy sequences FOR object IN SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema LOOP EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object; END LOOP; -- copy tables without constraints FOR object IN SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema and substr(table_name,1,4) <> 'src_' LOOP buffer := dest_schema || '.' || object; EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING DEFAULTS)'; FOR column_, default_ IN SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object LOOP EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ ||' DROP NOT NULL;'; END LOOP; END LOOP; -- copy tables with constraints FOR object IN SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema and substr(table_name,1,4) = 'src_' LOOP buffer := dest_schema || '.' || object; EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING ALL)'; -- copy sequences FOR column_, default_ IN SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)' LOOP sqls='ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_; RAISE NOTICE 'default=%',sqls; EXECUTE (sqls); END LOOP; END LOOP; RETURN true; EXCEPTION WHEN others THEN RETURN false; END; $BODY$ LANGUAGE plpgsql VOLATILE;
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
