On Mon, May 2, 2022 at 4:24 PM Robert Stanford <rstanford@xxxxxxxxx> wrote:
On Tue, 3 May 2022 at 08:39, David G. Johnston <david.g.johnston@xxxxxxxxx> 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 contactidwith 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.
David J.
David J.
