From: Richard Huxton <dev@archonet.com>
To: Kemin Zhou <kemin.zhou@ferring.com>, pgsql-sql@postgresql.org
Subject: Re: rule's behavior with join interesting
Date: Thu, 22 Apr 2004 10:49:01 +0100
User-Agent: KMail/1.5
References: <4086D465.1060907@ferring.com>
In-Reply-To: <4086D465.1060907@ferring.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200404221049.01201.dev@archonet.com>

On Wednesday 21 April 2004 21:07, Kemin Zhou wrote:
> Here I have a very simple case
>
> table1
> table1_removed
>
> anotherTable
>
> create or replace RULE rec_remove as ON DELETE TO table1
> do insert into table1_remove
> select old.*, a.acc from old g join anotherTable a on g.acc=a.other_acc;
> ===
> the parser complained       ERROR:  relation "*OLD*" does not exist
> So I used
> select old.*, a.acc from table1 g join anotherTable a on g.acc=a.other_acc;
>
> This worked find.
>
> When I run delete on table1, 213 rows.
>
> tmp table received 213X213 = 45369 rows.  each row is duplicated 213 times.

The issue here is that although you can refer to values such as OLD.acc, OLD 
is not a table but more like single row. So, you probably want
...DO INSERT INSTO table1_remove
SELECT old.*, a.acc FROM anotherTable a WHERE a.other_acc = OLD.acc;

Your second example just ignored the OLD.acc altogether in the join, so of 
course you got an unconstraind join of 213 x 213.
-- 
  Richard Huxton
  Archonet Ltd