Re: UUID vs serial and currval('sequence_id')

On Mon, May 2, 2022 at 3:33 PM Robert Stanford <rstanford(at)gmail(dot)com> wrote:

>
> --insert statement as single transaction returning contactid
> INSERT INTO contact(
> firstname, lastname)
> VALUES('John', 'Smith');
> INSERT INTO contactinterests(
> contactid, interest)
> VALUES (currval('contact_contactid_seq'),'Fishing')
> returning currval('contact_contactid_seq');
>
> Which is very nice as it gives us back the contactid.
>
> Is it possible to get similar functionality using gen_random_uuid() or
> uuid-ossp?
>
>
You basically have to use "INSERT ... RETURNING" or variables. Which/how
depends on the language you are writing in. Pure SQL without client
involvement requires that you use chained CTEs of INSERT...RETURNING (or I
suppose you could leverage set_config(), haven't tried that way myself).
In pl/pgsql you can also use variables, and the same goes for psql - though
that requires client involvement and so isn't generally that great a choice.

David J.