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