Brandon Craig Rhodes <brandon@oit.gatech.edu> writes:
> 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.
I hate to disappoint you, but they were doing the right thing. In
general, NEW and OLD are relations implicitly added to rule queries,
and so you were effectively doing something like
... FROM tab1 AS new, (select ... where ... x = new.x) AS sub
which is an illegal cross-FROM-entry reference.
With some just-committed patches, the error message is now along the
lines of "Subselect in FROM may not refer to other relations of same
query level" which may be more illuminating than "*OLD* does not exist".
> CREATE RULE number_insert AS
> ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
> EXCEPT SELECT 1;
But it's annoying that this case doesn't work. In an INSERT rule,
NEW.number isn't really a relation reference but a sort of macro formal
parameter, which will be replaced by the value inserted into the number
column. So, at least in the case where we're doing INSERT...VALUES,
the expanded query would be well-defined. I'm not convinced it would
work for INSERT...SELECT though :-(
regards, tom lane