Re: View's rule on delete problem

On Thu, Oct 28, 2004 at 04:32:21AM +0300, Stanislaw Tristan wrote:
>
> CREATE RULE "new_rule2" AS ON DELETE TO "public"."klients_view"
> DO INSTEAD (
>
> DELETE
> FROM klients
> WHERE (klients.klient_id = old.klient_id);
>
> DELETE
> FROM klient_services
> WHERE (klient_services.klient_id = old.klient_id);
> );
>
> It's 2 commands, but executing only first. Why?

Tom Lane described the problem in another thread a few years ago:

http://archives.postgresql.org/pgsql-general/2001-06/msg00559.php

"...OLD is essentially a macro for the view.  As soon as you delete
a row from foo, there's no longer any such row in the view, so the
delete from bar doesn't find anything to delete."

"What you probably want instead is to make bar reference foo as a
foreign key with ON DELETE CASCADE; then the rule for foobar only
needs to delete from foo explicitly, and the additional delete from
bar is done implicitly by the foreign key trigger."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/