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 22:05:05
Message-ID: 28616.1045173905@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:
> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2003-02-13 22:10:17 Re: index scan with index cond on first column doesn't recognize sort order of second column
Previous Message Damon Hart 2003-02-13 22:02:46 Re: backend process crash - PL/pgSQL functions - parsing problem?