Skip site navigation (1) Skip section navigation (2)

Re: View's rule on delete problem

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Stanislaw Tristan <stas7775(at)i(dot)com(dot)ua>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: View's rule on delete problem
Date: 2004-10-31 15:41:06
Message-ID: 20041031154106.GA97236@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
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/

In response to

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-10-31 15:42:10
Subject: Re: procedural languages in 7.4.6
Previous:From: Tom LaneDate: 2004-10-31 15:31:06
Subject: Re: Interpolation of environment variables in SQL at runtime?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group