[postgis-users] clone schema - pg_get_serial_sequence returns null

[Regina Obe]

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-bounces at lists.osgeo.org] On Behalf
Of Olivier Leprêtre
Sent: Sunday, July 30, 2017 2:24 PM
To: postgis-users at 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
can’t 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.

 

I’m 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;

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170731/92bcb16c/attachment.html>