Re: checking update/insert return

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