From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Chris Angelico <rosuav(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Chaining inserts ... This would be cool |
Date: | 2012-04-24 01:30:46 |
Message-ID: | 20120424013046.GA3903@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Angelico | 2012-04-24 01:43:21 | Re: Chaining inserts ... This would be cool |
Previous Message | Chris Angelico | 2012-04-23 22:12:10 | Re: Chaining inserts ... This would be cool |