Re: Joined table view - multiple delete action rule

Hi Jan and others,

thanks for the answer, this clears up the symptom.

In trying to rewrite the rules, I'm still facing the same problem.
I'll try to simplify the rules and tables (it's emulating the OO concept as
a is the parent and b and c are inherited from a)

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

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

CREATE VIEW ac AS
    SELECT a.x, a.y, c.v
    FROM a,c
    WHERE a.x=c.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);
);

/* cascading delete on a to b and c */

CREATE RULE a_del AS ON DELETE TO a DO (
    DELETE FROM b WHERE (x=old.x);
    DELETE FROM c WHERE (x=old.x);
);

/* delete on view doesn't work */

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

The last rule seems to have the same effect as the original rule where I
implemented the cascading delete on the delete rule for the ab-view.
So I suppose the query rewriter will end up executing the same sequence of
queries.

Now, is there a way to implement this delete on the joined view?

Thanks,

Lieven