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

checking update/insert return

From: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: checking update/insert return
Date: 2004-01-05 20:54:09
Message-ID: 55788.200.161.200.85.1073336049.squirrel@webmail.webnow.com.br (view raw or flat)
Thread:
Lists: pgsql-novice
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 (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" ???

I did some research and found an internal function called FOUND, but
it seems to work only with select.

In short, how to determine if an insert (or update) clause has
ended with success? The backend seems to indicate this, by a INSERT
return...

Any help is welcome

Thanks.




Responses

pgsql-novice by date

Next:From: Bruno LEVEQUEDate: 2004-01-05 21:22:25
Subject: Re: checking update/insert return
Previous:From: AndreasDate: 2004-01-05 18:08:00
Subject: Re: postgresql in windows

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