From: Richard Huxton <dev@archonet.com>
To: Kemin Zhou <kemin.zhou@ferring.com>
To: pgsql-sql@postgresql.org
Subject: Re: rule's behavior with join interesting
Date: Thu, 22 Apr 2004 10:49:01 +0100
Message-ID: <200404221049.01201.dev@archonet.com> (raw)
In-Reply-To: <4086D465.1060907@ferring.com>
References: <4086D465.1060907@ferring.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, kemin.zhou@ferring.com
Subject: Re: rule's behavior with join interesting
In-Reply-To: <200404221049.01201.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox