Re: DO INSTEAD and conditional rules

On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:

> Well, they handle simple situations OK, but we keep seeing people get
> burnt as soon as they venture into interesting territory. For
> instance,
> if the view is a join, you can't easily make a rule that turns a delete
> into deletions of both joined rows. And you'll get burnt if you try to
> insert any volatile functions, because of the multiple-evaluation
> issue.
> Etc.

sharky=# CREATE TABLE a (
sharky(# id int,
sharky(# name text
sharky(# );
CREATE TABLE
sharky=# CREATE TABLE b (
sharky(# a_id int,
sharky(# rank text
sharky(# );
CREATE TABLE
sharky=#
sharky=# CREATE VIEW ab AS
sharky-# SELECT id, name, rank
sharky-# FROM a, b
sharky-# WHERE a.id = b.a_id
sharky-# ;
CREATE VIEW
sharky=# CREATE RULE delete_ab AS
sharky-# ON DELETE TO ab DO INSTEAD (
sharky(# DELETE FROM b
sharky(# WHERE a_id = OLD.id;
sharky(#
sharky(# DELETE FROM a
sharky(# WHERE id = OLD.id;
sharky(# );
CREATE RULE
sharky=#
sharky=#
sharky=# insert into a values (1, 'test');
INSERT 597795 1
sharky=# insert into b values (1, 'sergeant');
INSERT 597796 1
sharky=# select * from ab;
id | name | rank
----+------+----------
1 | test | sergeant
(1 row)

sharky=# delete from ab;
DELETE 0
sharky=# select * from ab;
id | name | rank
----+------+------
(0 rows)

sharky=# select * from a;
id | name
----+------
1 | test
(1 row)

sharky=# select * from b;
a_id | rank
------+------
(0 rows)

Ah, yes, you're right, that is...unexpected. Perhaps OLD can contain
its values for the duration of the RULE's statements? I'm assuming that
what's happening is that OLD.id is NULL after the first of the two
DELETE statements...

> Like I said, I don't have a better idea. Just a vague feeling of
> dissatisfaction.

I'd call it a bug. ;-)

Regards,

David