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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> writes:

> > We had been avoiding sub-selects within rules because they could not
> > reference the NEW and OLD pseudo-relations ...

> I hate to disappoint you, but they were doing the right
> thing. ... you were effectively doing ... an illegal
> cross-FROM-entry reference.
...
> > 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.

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. To quote from my original
email, I should reiterate that

>CREATE RULE number_insert AS
>ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
> EXCEPT SELECT 1;

*does* work, while

>CREATE RULE number_insert_fails AS
>ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;

does *not* work.

My confusion is that in both cases NEW.number is used in a scalar
context and thus, according to your `macro' argument, *both* of these
cases should work, not just in the first case. I fail to see how the
slight difference between these two cases makes the first rule's
reference to NEW legitimate and the second one's not.

In other words these two rules are similar enough that it seems to me
they should stand or fall together: either your argument about the
cross-FROM expression should eliminate both of them as possibilities,
or your argument about NEW.number being a macro should allow both.

Thanks for looking at this,
--
Brandon Craig Rhodes http://www.rhodesmill.org/brandon
Georgia Tech brandon(at)oit(dot)gatech(dot)edu