agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed
From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Get last generated serial sequence and set it up when explicit value is used
Date: Sat, 21 Nov 2020 10:26:07 +0100
Message-ID: <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com> (raw)
In-Reply-To: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
References: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>

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





view thread (7+ messages)  latest in thread

Message-ID: <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>
Permalink:  ../d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com/
Also on:    postgresql.org/message-id/d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sf@4js.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Get last generated serial sequence and set it up when explicit value is used
  In-Reply-To: <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox