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

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