Re: rule's behavior with join interesting

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