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

Re: checking update/insert return

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 (view raw or flat)
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


In response to

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2004-01-05 21:31:28
Subject: Re: checking update/insert return
Previous:From: Marcus Andree S. MagalhaesDate: 2004-01-05 20:54:09
Subject: checking update/insert return

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