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 );
It works but "returning contactuuid" is considerably easier to understand and probably cheaper to execute.
If you are going to pre-compute the uuid the returning clause becomes pointless though, as your example demonstrates - you never actually use the returned value.
I suggest avoiding naming the CTE query and the column(s) it produces the same thing.
