On 06/14/2014 09:35 PM, Tom Lane wrote: > 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? Was'nt there a plan (consensus?) about deprecating rules altogether ?
Cheers Hannu > 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 > > -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
