Re: checking update/insert return

Hi, thanks for your message.

>> 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 :-(
>

I see the problem now. It's amazing how simple tasks can be really
funny... ;)

>> 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.
>

Yeah, but that's why I can't use it... Must provide really sequencial
numbers, without holes.
This all leads me to another question... when we use psql, we can tell
that out insert was OK because a number (OID?) is returned to the client.
Maybe other client interfaces can also access the OID of the just-inserted
data as a mean of checking the success of the entire operation and this
problem will be solved entirely in the client.