Re: Get last generated serial sequence and set it up when explicit value is used

Better use >= in pkey >= (select last_value ... ) :

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 );

Seb

On 11/21/20 10:26 AM, Sebastien FLAESCH wrote:
> 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
>