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.Elphick@lfix.co.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