Hi Dean,
> However, with the proposed patch, this produces "Output: x.a, foo.b,
> old.c", where that first "x.a" comes from the rule action's "x" alias,
> and so actually means new.a, but a user might reasonably think it
> meant old.a, since they specified OLD AS x.
I built master without the Assert()s, Tom's fragment and your
change, and tried them on the examples in this thread and a few
more. Neither fix gives what I would call the best result. But
for now, choosing the second best is the best we can do, and
I think that is Tom's fragment.
RETURNING WITH is there to avoid name clashes. When the rule
and the query use the same name, no fix can tell them apart
("x" in your example, "n" in mine upthread). What matters is
whether the output stays distinguishable when the user avoids
clashes. Tom's fragment only uses names that the rule action or
the query actually has, so it does.
For example, with the rule and the query both using "y":
CREATE TABLE t (a int, b int);
CREATE VIEW v AS SELECT * FROM t;
CREATE RULE r AS ON UPDATE TO v DO INSTEAD
UPDATE t SET a = new.a, b = new.b WHERE a = old.a
RETURNING WITH (OLD AS y) y.a, t.b;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v AS old SET a = old.a + 100
RETURNING WITH (NEW AS y) y.a AS new_a, a, b;
without the Assert()s: y.a, a, t.b
Tom's fragment: y.a, y.a, t.b
your change: y.a, old.a, t.b
and with "y1" in the rule and "y2" in the query instead:
CREATE OR REPLACE RULE r AS ON UPDATE TO v DO INSTEAD
UPDATE t SET a = new.a, b = new.b WHERE a = old.a
RETURNING WITH (OLD AS y1) y1.a, t.b;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v AS old SET a = old.a + 100
RETURNING WITH (NEW AS y2) y2.a AS new_a, a, b;
without the Assert()s: y2.a, a, t.b
Tom's fragment: y2.a, y1.a, t.b
your change: y2.a, old.a, t.b
Tom's fragment cannot tell the two "y"s apart, but once the user
avoids the clash it prints the names as written. Your change
prints "old.a" either way, and "old" is what the query calls the
view, not a name anyone gave to OLD.
The cost is that the rule's alias wins when both the rule and
the query name OLD or NEW, so a user who names them in the
query can see other names in EXPLAIN. As Tom said, the manual
already steers rule actions away from OLD and NEW in RETURNING,
so I think that is acceptable.
Best regards,
Henson
