Re: Chaining inserts ... This would be cool

On Tue, Apr 24, 2012 at 08:12:10AM +1000, Chris Angelico wrote:
> 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? :)

One crucial difference is the number of round trips to the database.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate