Get last generated serial sequence and set it up when explicit value is used - Mailing list pgsql-sql

Hi all!

Using SERIAL or BIGSERIAL column, I try to find a smart solution to
do the following when an INSERT is done:

1) Retrieve the last generated sequence, so the program can use it.

2) Setup the underlying sequence, if an explicit value was used by
the INSERT statement.

So far I figured out the following by using the RETURNING clause...

Is this ok / legal / without risk? (when multiple users insert rows at the same time?)


test1=# create table table1 ( pkey serial not null primary key, name varchar(50) );
CREATE TABLE


test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
     1 |          1
(1 row)

INSERT 0 1

test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
     2 |          2
(1 row)

INSERT 0 1

test1=# insert into table1 (pkey,name) values (100,'aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
   100 |          2
(1 row)

INSERT 0 1



I see 100 is > than 2, so reset the sequence:

test1=# select setval('table1_pkey_seq',101,false);
  setval
--------
     101
(1 row)

test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
   101 |        101
(1 row)

INSERT 0 1




Any better way to do that in a single SQL statement?


Is it legal to use a subquery in a RETURNING clause?


Thanks!
Seb