Re: Joined table view - multiple delete action rule

Hi Andrew,

I'm sorry but I was a but sloppy in typing. The right version is

CREATE TABLE a (x integer PRIMARY KEY,y integer);
CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x,z))

CREATE VIEW ab AS
SELECT a.x, a.y, b.z
FROM a,b
WHERE a.x=b.x;

/* this -insert- seems to work */

CREATE RULE ab_ins AS ON INSERT TO ab DO INSTEAD (
INSERT INTO a(x,y) VALUES (new.x, new.y);
INSERT INTO b(x,z) VALUES (new.x, new.z);
);

/* this -delete- does not work */

CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD (
DELETE FROM b WHERE (x=old.x) AND (z=old.z);
DELETE FROM a WHERE (x=old.x);
);

So I already got the answer:

the query rewriter only takes the first action after which the joined row
doesn't exist anymore and thus the second delete doesn't affect the tables
anymore.

I'm now trying to rewrite this as a cascading delete rule on table a;

Greetings

Lieven