Re: Chaining inserts ... This would be cool

You can have INSERT ... SELECT .... RETURNING ... as a query in Postgres.
The RETURNING applies to the INSERT. See:

CREATE TABLE foo(i INT);
INSERT INTO foo(i) SELECT generate_series(1,5) RETURNING i;

Anyway, CTEs might work, but I can't see any good way to pass the
association through without the syntax or equivalent to what I originally
posted. Thanks for the help anyway.

On Mon, Apr 23, 2012 at 4:19 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> On 23 April 2012 21:49, 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;
> >
> > Anyway, I'm sure there are more important features for Postgres (like
> > upserts, unique indexes on GIN, Gist and hash, fixed arrays, compact
> storage
> > of enum arrays as bitfields, etc.) I just thought it was an interesting
> > idea.
>
> You should be able to use writeable common table expressions to
> achieve a linking behaviour.
>
> http://www.postgresql.org/docs/9.1/static/queries-with.html
> http://www.depesz.com/index.php/2011/03/16/waiting-for-9-1-writable-cte/
>
> http://thombrown.blogspot.de/2011/11/writeable-common-table-expressions.html
>
> But I'm not sure the query you posted makes any sense. Why would a
> SELECT statement have a RETURNING clause? And where do the values for
> the first INSERT come from?
> --
> Thom
>