Call sequence in a create table stored in a variable

Question

Call sequence in a create table stored in a variable

Navigation

#1 by (0 votes)

1

Hello have the following problem: I have created a sequence in a trigger, and then dynamically instantiate it by create statements of tables in the same trigger. When I try to put NEXTVAL('secuencia') the single quotes tell me the variable, which I can do in this case because they are necessary to be able to call the sequence:

Sequence

sql := 'CREATE SEQUENCE mod_riestadistica.sec_'||nombretabla||'_seq
          INCREMENT 1
          MINVALUE 1
          MAXVALUE 9223372036854775807
          START 1
          CACHE 1;
          ALTER TABLE public.sec_'||nombretabla||'
          OWNER TO postgres;';
          execute sql;

sql := 'CREATE TABLE public.t_'||nombretabla||'('||nombre_id||' numeric NOT NULL DEFAULT NEXTVAL ("public"."sec_' || nombretabla||'"), CONSTRAINT pk_' ||nombretabla||' PRIMARY KEY ('||nombre_id||' ) )
          WITH ( OIDS=FALSE); ALTER TABLE public.t_'||nombretabla||' OWNER TO postgres;';

execute sql;

sql postgresql trigger pgadmin

asked by Alberto Blanco Cala 17.01.2018 в 21:08

source

1 answer

Pass id value selected in dropdowlist to the controller to filter webgrid problem with the ionic service code [closed]

score 0 · Answer 1

In your question there is a contradiction, because

you create the sequence mod_riestadistica.sec_'||nombretabla||'_seq
then alter the table public.sec_'||nombretabla||'
finally you want to assign the value of NEXTVAL ("public"."sec_' || nombretabla||'") by default

that will not work. You are telling him to use NEXTVAL on a table.

Assuming that you really want to assign the default NEXTVAL of mod_riestadistica.sec_'||nombretabla||'_seq the second statement should contain:

DEFAULT NEXTVAL (''"mod_riestadistica"."sec_' || nombretabla||'_seq"'')

And with that you can reference sequences of other schemas. When executing that, in the definition of the created table you would see that the default value (assuming the table is called test table ) is:

nextval('mod_riestadistica.sec_tablaprueba_seq'::regclass)