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

Hello everyone!

I believe I have a solution using a single SQL command.

Check this out... do you see any potential issues?

Any simpler way or more efficient code?

Note the insert with value 50, lower than previously inserted values, otherwise,
the returning clause would just need to be

         returning pkey, setval('mytab1_pkey_seq',pkey,true)

Note also that I want to return the pkey to use the generated serial in the
program code...


=====

create table mytab1 ( pkey serial not null primary key, name varchar(50) );

insert into mytab1 (name)      values ('aaa')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (pkey,name) values (100,'bbb')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (name)      values ('ccc')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (pkey,name) values (50,'ddd')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (name)      values ('eee')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

select * from mytab1 order by name;

=====

SELECT output:

  pkey | name
------+------
     1 | aaa
   100 | bbb
   101 | ccc
    50 | ddd
   102 | eee
(5 rows)


PostgreSQL rocks!

Seb