As I mentioned awhile ago, I'm thinking about implementing the
SQL-standard construct
UPDATE foo SET ..., (a,b,...) = (SELECT x,y,...), ...
I've run into a rather nasty problem, which is how does this interact
with expansion of NEW references in ON UPDATE rules? For example,
suppose foo has a rule
ON UPDATE DO ALSO INSERT INTO foolog VALUES (new.a, new.b, ...);
The existing implementation relies on being able to pull expressions
for individual fields' new values out of the UPDATE targetlist; but
there is no independent expression for the new value of "a" here.
Worse yet, the NEW references might be in WHERE quals, or some other
place outside the targetlist of the rule query, which pretty much
breaks the implementation I'd sketched earlier.
The best that I think is reasonable to do in such cases is to pull out
a separate copy of the sub-select for each actual NEW reference in a
rule query. So the example above would give rise to an expanded
rule query along the lines of
INSERT INTO foolog VALUES ( (SELECT x as a, y as b, ...).a, (SELECT x as a, y as b, ...).b,
... );
which would work, but it would re-evaluate the sub-select more times
than the user might be hoping. (Of course, if there are volatile
functions in the sub-select, he's screwed, but that's not a new
problem with rules.)
Given that ON UPDATE rules are close to being a deprecated feature,
it doesn't seem appropriate to work harder than this; and frankly
I don't see how we could avoid multiple sub-select evaluations anyway,
if the NEW references are in WHERE or other odd places.
Another possible answer is to just throw a "not implemented" error;
but that doesn't seem terribly helpful, and I think it wouldn't save
a lot of code anyway.
Thoughts?
regards, tom lane