Обсуждение: Setting a default for nextval sequence

I'm attempting to set the default value for a serial column. I created a generic function that I am passing a table name as the only parameter. I had it working correctly, however, it does not seem to like the sequence name being the same name for each audit table that is created through the function. So I changed the code to include the table name as part of the naming convention. Now, I need help on how to alter the serial column the new value EXECUTE 'CREATE SEQUENCE '|| t_name ||'tab_id_seq'|| ' OWNED BY '|| t_name || '_cdc'||'.table_id'; EXECUTE 'ALTER TABLE ' || quote_ident(t_name || '_cdc') || ' ALTER COLUMN table_id SET DEFAULT nextval(''tab_id_seq'');';

On Mon, Nov 27, 2017 at 12:13 PM, Robert Lakes wrote: > I'm attempting to set the default value for a serial column. I created a > generic function that I am passing a table name as the only parameter. I > had it working correctly, however, it does not seem to like the sequence > name being the same name for each audit table that is created through the > function. > So I changed the code to include the table name as part of the naming > convention. Now, I need help on how to alter the serial column the new > value > > EXECUTE 'CREATE SEQUENCE '|| t_name ||'tab_id_seq'|| ' OWNED BY '|| t_name > || '_cdc'||'.table_id'; > > EXECUTE 'ALTER TABLE ' || quote_ident(t_name || '_cdc') || ' ALTER > COLUMN table_id SET DEFAULT nextval(''tab_id_seq'');'; > ​Not tested but:​ ​EXECUTE format('CREATE SEQUENCE %I OWNED BY %I.table_id', t_name || 'tab_id_seq', t_name ||​ '_cdc'); EXECUTE format('ALTER TABLE %I ALTER COLUMN table_id SET DEFAULT nextval(%L);', t_name || '_cdc', t_name || 'tab_id_seq'); ​David J.

On Mon, Nov 27, 2017 at 2:24 PM, David G. Johnston < david.g.johnston@gmail.com> wrote: > On Mon, Nov 27, 2017 at 12:13 PM, Robert Lakes > wrote: > >> I'm attempting to set the default value for a serial column. I created a >> generic function that I am passing a table name as the only parameter. I >> had it working correctly, however, it does not seem to like the sequence >> name being the same name for each audit table that is created through the >> function. >> So I changed the code to include the table name as part of the naming >> convention. Now, I need help on how to alter the serial column the new >> value >> >> EXECUTE 'CREATE SEQUENCE '|| t_name ||'tab_id_seq'|| ' OWNED BY '|| >> t_name || '_cdc'||'.table_id'; >> >> EXECUTE 'ALTER TABLE ' || quote_ident(t_name || '_cdc') || ' ALTER >> COLUMN table_id SET DEFAULT nextval(''tab_id_seq'');'; >> > > ​Not tested but:​ > > ​EXECUTE format('CREATE SEQUENCE %I OWNED BY %I.table_id', > t_name || 'tab_id_seq', > t_name ||​ '_cdc'); > > EXECUTE format('ALTER TABLE %I ALTER COLUMN table_id SET DEFAULT > nextval(%L);', > t_name || '_cdc', > t_name || 'tab_id_seq'); > > ​David J. > > *Do you mean ?* *https://www.postgresql.org/docs/9.6/static/functions-sequence.html * *SELECT setval(regclass, bigint) --- Set sequence's current value* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.