Thread: new rows based on existing rows

Hi,

I frequently have a need to insert new rows into a table that are
based on existing rows but with small changes.

This is easy using something like

insert into foo (a,b,foo_date)
  select a,b,now() from foo old where ....
  returning oid

However in the application layer, I need to know which new record
corresponds with which original record

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?

Cheers,
Andy

--
Andy Chambers
Software Engineer
(e) achambers@mcna.net
(t) 954-682-0573

CONFIDENTIALITY NOTICE: This electronic mail may contain information
that is privileged, confidential, and/or otherwise protected from
disclosure to anyone other than its intended recipient(s). Any
dissemination or use of this electronic mail or its contents by
persons other than the intended recipient(s) is strictly prohibited.
If you have received this communication in error, please notify the
sender immediately by reply e-mail so that we may correct our internal
records. Please then delete the original message. Thank you.

On Wed, May 2, 2012 at 10:52 PM, Andy Chambers <achambers@mcna.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

Andy Chambers <achambers@mcna.net> writes:
> 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.

Hm ... it is kind of annoying that that doesn't work, seeing that
comparable locutions do work in UPDATE ... FROM ... RETURNING and
DELETE ... USING ... RETURNING.  And I think that the engine could
actually do it easily enough.  The problem is more of a SQL standards
theoretic one: a sub-SELECT doesn't expose anything beyond its result
columns.  So the fact that foo might have some other columns besides
what you selected for use in the INSERT isn't visible from "outside"
the sub-SELECT.

I don't see any way to fix this directly (ie, letting RETURNING "look
inside" the sub-SELECT) that wouldn't be a horrid violation of both
the letter and spirit of the SQL standard, not to mention logically
inconsistent in assorted ways.  Possibly we could allow the sub-SELECT
to return more columns than the INSERT needs, but that seems like a
pretty error-prone "feature".

For the moment I think all you could really do is what somebody else
suggested, namely eat the overhead of having an "old_id" column in
the table so that you can insert the value you want into that column,
thus making it available to the RETURNING clause.

            regards, tom lane

On 3 May 2012, at 24:00, Tom Lane wrote:

> Andy Chambers <achambers@mcna.net> writes:
>> 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.
>
> For the moment I think all you could really do is what somebody else
> suggested, namely eat the overhead of having an "old_id" column in
> the table so that you can insert the value you want into that column,
> thus making it available to the RETURNING clause.


I was wondering, would an updatable view with a "pseudo-column" for the old_id do it?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.