On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson <apperson@xxxxxxxxx> wrote: > There are obviously workarounds for this, but I'm wondering why the > following query shouldn't work. It seems like it should. With MVCC already > present on the back-end, I can't see any reason other than additional > parsing routines that this couldn't work: > > INSERT INTO old_login_id_to_new_account_id(new_account_id, old_login_id) > INSERT INTO accounts(id, username, password_hash, email) SELECT DEFAULT, > username, password_hash, email FROM logins_old RETURNING id, logins_old.id; That's possible using WITH. I made a statement that creates an invoice and its lines (with the lines all having a foreign-key reference to the owning invoice) more or less the same way: WITH inv AS (insert into ... returning id), constants AS (values (...),(...),(...)) INSERT INTO invoicelines (columnlist) SELECT inv.id,constants.* FROM inv,constants Something like that. I do remember running into trouble with the multi-row insert (can't use multiple rows of literals with SELECT, and can't fetch data from a WITH expression with VALUES), so it had to go to the extra level of structure. If you're inserting just one row into each, this should be easy. Of course, the question I never asked (never bothered to, really) was: Is it really any better than simply doing the first insert and retrieving the ID in my application? :) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
