Re: bug - NEW and OLD in sub-selects in rules

Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> writes:
> My original posting must not have been clear: the case you quote
> above, which you are annoyed `doesn't work', is in fact one of the two
> rules in my example which *does* work.

Not any more ;-). As of CVS tip:

regression=# create table numbers (number int);
CREATE TABLE
regression=# CREATE RULE number_insert_fails AS
regression-# ON INSERT TO numbers DO
regression-# SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;
ERROR: Subselect in FROM may not refer to other relations of same query level
regression=# CREATE RULE number_insert AS
regression-# ON INSERT TO numbers DO
regression-# SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
regression-# EXCEPT SELECT 1;
ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level

In my opinion this restriction is essential for the OLD case. It might
not be essential for NEW in an ON INSERT rule, but I'm not sure.

regards, tom lane