Thanks to Tom's excellent work in producing a patch in response to our
question on Monday, we have been able to move forward and uncover
another problem.
We had been avoiding sub-selects within rules because they could not
reference the NEW and OLD pseudo-relations (it would tell us `Relation
"*NEW*" does not exist' and so forth), which we assumed was because of
some obscure scoping limitation with respect to those two relations.
Imagine our surprise when we discovered that, once we started using
the EXCEPT clauses that Tom's Monday patch fixed, we were also able to
use NEW and OLD within sub-queries - but *only* if the parent select
contained an EXCEPT clause!
The following complete test case attempts to create four rules, which
are identical except that the SELECTS of the first and third are
supplemented with EXCEPT clauses. The result? The first and third
can use NEW and OLD just fine, while the second and fourth cannot.
Our guess is that this is a bug.
CREATE TABLE numbers ( number INTEGER );
CREATE RULE number_insert AS
ON INSERT TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
EXCEPT SELECT 1;
CREATE RULE number_insert_fails AS
ON INSERT TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;
CREATE RULE number_delete AS
ON DELETE TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = OLD.number) AS sub
EXCEPT SELECT 1;
CREATE RULE number_delete2 AS
ON DELETE TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = OLD.number) AS sub;
--
Brandon Craig Rhodes http://www.rhodesmill.org/brandon
Georgia Tech brandon@oit.gatech.edu
