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.