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: 1073338288.25140.1389.camel@linda.lfix.co.uk (view raw or flat)
Thread:
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
assigned.

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                             http://www.lfix.co.uk/oliver
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

Responses

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-2014 The PostgreSQL Global Development Group