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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: bug - NEW and OLD in sub-selects in rules
Date: 2003-02-13 20:57:47
Message-ID: 26996.1045169867@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 (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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Stump 2003-02-13 21:16:10 nodeRead: did not find '}' at end of plan node
Previous Message Yolanda Valverde 2003-02-13 20:57:27 How to create stored procedure in PostgreSQL with plpgsql?