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(at)Yahoo(dot)com #

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