Skip site navigation (1) Skip section navigation (2)

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

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-hackers(at)postgresql(dot)org
Subject: Re: possibly spurious `EXCEPT ... may not refer to other relation...'
Date: 2003-02-21 15:13:18
Message-ID: 14821.1045840398@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> 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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-02-21 15:27:29
Subject: Re: Loss of cluster status
Previous:From: Joe ConwayDate: 2003-02-21 14:56:15
Subject: Re: contrib Makefile's and OS X

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group