From: | Bruno LEVEQUE <bruno(dot)leveque(at)net6d(dot)com> |
---|---|
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:22:25 |
Message-ID: | 3FF9D591.6000102@net6d.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(dot)leveque(at)net6d(dot)com
http://www.net6d.com
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2004-01-05 21:31:28 | Re: checking update/insert return |
Previous Message | Marcus Andree S. Magalhaes | 2004-01-05 20:54:09 | checking update/insert return |