Re: Problems with RULE

On 3/6/01, 7:11:48 AM, Jens Hartwig <jens.hartwig@t-systems.de> wrote 
regarding [SQL] Problems with RULE:

> Hello all,

> I tried to implement the following rule: if someone wants to delete a 
record
> from a table t_xyz (id integer, deleted boolean) the record should get a
> delete-flag (deleted = true). When this "pre-deleted" record is deleted 
for
> the next time it should be physically deleted from the database.

> I implemented the following rule:

>   CREATE RULE r_del_xyz
>   AS ON DELETE TO t_xyz WHERE (old.deleted = false)
>   DO INSTEAD
>     UPDATE t_xyz
>     SET deleted = true
>     WHERE id = old.id;

> Now I tested the new rule:

>   INSERT INTO t_xyz VALUES (1, false);
>   INSERT INTO t_xyz VALUES (2, false);
>   DELETE FROM t_xyz WHERE id = 1;
>   SELECT * FROM t_xyz ;

>    id | deleted
>   ----+---------
>     2 | f

> What has happened? The rule seems to be ignored and the record was 
deleted!

No help I'm afraid, but I encountered something similar the other day on 
7.1b3

CREATE RULE ... AS ON UPDATE TO ... WHERE ... DO INSTEAD UPDATE ...

Didn't run, but removing the WHERE did. I had thought someone had raised 
this recently, but looking through the list I can't see it now, so maybe 
it's a real bug. Is there any mention of this is the CHANGES file in 
beta4?

I'll try and check this end whether it applies to all rule-types if you 
add a WHERE.

- Richard Huxton