Re: Joined table view - multiple delete action rule

Lieven Van Acker wrote:
> 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?

    That's  not  what I suggested, it's still using rules for the
    cascaded delete. I meant to setup a  FOREIGN  KEY  constraint
    with  an  ON  DELETE CASCADE referential action. Add to table
    "b" and "c"

        ON DELETE CASCADE

    after the REFERENCES keyword and leave out the  entire  a_del
    rule.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com