Skip site navigation (1) Skip section navigation (2)

Re: checking update/insert return

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: checking update/insert return
Date: 2004-01-05 21:31:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Mon, 2004-01-05 at 20:54, Marcus Andree S. Magalhaes wrote:
> Dear list,
> Is there any way to check a successful insertion or update on a table
> in plpgsql?
> We have a highly concurrent system here, and we want to return a valid
> and unique ID to the caller, like the following pseudo code (no flames,
> didatic use only ;-):
> while (true)
>  found = select count (*) from mytable where id = $id
>  if (found == 0) /* non existent id */
>      insert into mytable (id. name) values ($id, $name)
>      /* someone in parallel could have inserted the same id before
>         so we need to check if this insertion was OK, but how??? */

If it already existed, this insertion would fail and would abort the
current transaction.  Therefore finding that out at this stage would be
academic :-( 

>      if (INSERTED) return $id /* we inserted our id with success */
>      else $id = $id + 1 /* someone has used this id, increment it
>                            and try again */
>      end if
>  else
>      $id = $id + 1 /*id already exists*/
>  end if
> end while
> My question is, is there any postgres internal boolean function
> that somewhat resembles what I described here as "INSERTED" ???

The normal way of doing this is to use a sequence:
   declare the column of type SERIAL;
   insert the value DEFAULT;
   SELECT currval('schema.table_column_seq') to get the value just

The only problem with that is that it does not guarantee a continuous
sequence of ids in the table; some applications do require that and need
some other mechanism to achieve it.

Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK                   
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
     "And thou shalt love the LORD thy God with all thine 
      heart, and with all thy soul, and with all thy might."
                 Deuteronomy 6:5 

In response to


pgsql-novice by date

Next:From: Marcus Andree S. MagalhaesDate: 2004-01-05 22:40:32
Subject: Re: checking update/insert return
Previous:From: Bruno LEVEQUEDate: 2004-01-05 21:22:25
Subject: Re: checking update/insert return

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group