To: pgsql-sql@lists.postgresql.org
From: Sebastien FLAESCH <sf@4js.com>
Subject: Get last generated serial sequence and set it up when explicit value
 is used
Organization: Four Js Development Tools
Message-ID: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
Date: Thu, 19 Nov 2020 20:21:39 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.4.2
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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