Andy Chambers <achambers@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
