Re: [HACKERS] possibly spurious `EXCEPT ... may not refer to other relation...'

[Tom Lane]

Brandon Craig Rhodes [EMAIL PROTECTED] writes:
 The current CVS version of PostgreSQL gives us the error:
 ERROR:  UNION/INTERSECT/EXCEPT member statement
 may not refer to other relations of same query level

 when given the following test case, despite the fact that the EXCEPT
 clause does not refer to any other relation involved in the same
 query.

 CREATE OR REPLACE RULE current_delete AS
 ON DELETE TO current
 DO INSERT INTO former (number)
  SELECT number FROM current
   WHERE number = OLD.number
  EXCEPT
   SELECT number FROM trash;

But in fact OLD is a relation reference.  You have to remember that when
you issue, say,
DELETE FROM current WHERE date  '2002-02-01'
(I'm just making up an example of a WHERE-condition here), the rule
action gets rewritten to something like
INSERT INTO former (number)
SELECT current.number FROM current, current OLD
WHERE current.number = OLD.number
AND OLD.date  '2002-02-01'
I've left off the EXCEPT part in my example of the rewritten query,
because I'm not sure where the OLD reference could get put if the
rule action involves an EXCEPT.  The code doesn't know either :-(

You may find that the most practical way to handle this requirement
is to put the insertion-into-former command into a trigger procedure
rather than a rule.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[HACKERS] possibly spurious `EXCEPT ... may not refer to other relation...'

[Brandon Craig Rhodes]

The current CVS version of PostgreSQL gives us the error:

ERROR:  UNION/INTERSECT/EXCEPT member statement
may not refer to other relations of same query level

when given the following test case, despite the fact that the EXCEPT
clause does not refer to any other relation involved in the same
query.  We suspect this to be a bug.  (This simple test case has been
drastically reduced from the actual code we want to run, which is a
more complicated INSERT INTO SELECT ... EXCEPT whose goal is to avoid
inserting rows that are already in the destination table; so whereas
here the except clause involves a third, dummy table, we would
actually like to check for whether the row exists in the table which
is the target of the INSERT.)

CREATE TABLE current ( number INTEGER );
CREATE TABLE former ( number INTEGER );
CREATE TABLE trash ( number INTEGER) ;

CREATE OR REPLACE RULE current_delete AS
ON DELETE TO current
DO INSERT INTO former (number)
 SELECT number FROM current
  WHERE number = OLD.number
 EXCEPT
  SELECT number FROM trash;

-- 
Brandon Craig Rhodes http://www.rhodesmill.org/brandon
Georgia Tech[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]