For this particular scenario, can't you just create two ON DELETE rules? The first would delete from b, the second from a. Perhaps an example with a scenario like this can be added to the doc's?
So, the short answer is you can only perform one query in a rule, but you can have multiple rules defined to do what you need. Can you call a stored proc from a rule? You could pass the old.id to the stored proc and do as many queries as you like in there without worry that the old.id would go away. Just some thoughts. It does suck that old.id goes away. Any way of preventing that from happening? 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!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
