Are rules even needed anymore? Can't you do this all with triggers? If you want to "DO INSTEAD" just use a row based trigger, and return null. Or is this less efficient?
Later Rob --- David Wheeler <[EMAIL PROTECTED]> wrote: > 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 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org