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

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