Re: Chaining inserts ... This would be cool

On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson <apperson(at)gmail(dot)com> 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