Hi,
Why do you not use the serial data type : SERIAL ?
EX. :
create table mytable (
id SERIAL,
name text); <- or anything else
So, when you want to insert data your request becomes :
insert into mytable (name) values ($name)
And you are sure that id is unique (you do not need your loop, your
tests, ...).
Bruno
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 (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.
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com