On Mon, May 2, 2022 at 4:24 PM Robert Stanford <rstanf...@gmail.com> wrote:
> On Tue, 3 May 2022 at 08:39, David G. Johnston <david.g.johns...@gmail.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 ); > > 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.
