Re: new rows based on existing rows

On Wed, May 2, 2012 at 10:52 PM, Andy Chambers <achambers(at)mcna(dot)net> wrote:
> So ideally, I'd like to be able to do
>
> insert into foo (a,b,foo_date)
>  select a,b,now() from foo old where ....
>  returning oid, old.oid
>
> ...but this doesn't work.  It seems you only have access to the table
> being modified in a returning clause.  Is there a way I can return a
> simple mapping between old oids and new oids as part of the statement
> that inserts the new ones?

I'd recommend not using OIDs but having your own ID field (eg a
[BIG]SERIAL PRIMARY KEY). Is the mapping of old ID to new ID something
that would be worth saving into the table? Even if you don't need it
later, that might be the easiest way to do the job. Alternatively, you
could play around with joins (an INSERT RETURNING can quite happily be
used in a WITH clause) to see if you can get what you want that way.

ChrisA