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

On Tue, 3 May 2022 at 08:39, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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.
>
>
Thanks, so I can do:

alter table contact add column contactuuid uuid
alter table contactinterests add column contactuuid uuid
alter table contactinterests drop column contactid

with thisuuid as (
SELECT gen_random_uuid() as thisuuid
),
contactuuid as(
INSERT INTO contact(
contactuuid,firstname, lastname)
VALUES(
(select thisuuid from thisuuid ),'John', 'Smith') returning
(select thisuuid from thisuuid )
)
INSERT INTO contactinterests(
contactuuid, interest)
VALUES (
(select thisuuid from contactuuid ),'Fishing')
returning (select thisuuid from contactuuid );

Robert