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

Re: DO INSTEAD and conditional rules

From: David Wheeler <david(at)kineticode(dot)com>
To: Rob Butler <crodster2k(at)yahoo(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Neil Conway <neilc(at)samurai(dot)com>,Jan Wieck <JanWieck(at)yahoo(dot)com>
Subject: Re: DO INSTEAD and conditional rules
Date: 2005-04-26 21:53:38
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Apr 26, 2005, at 2:43 PM, Rob Butler wrote:

> For this particular scenario, can't you just create
> two  ON DELETE rules?  The first would delete from b,
> the second from a.  Perhaps an example with a scenario
> like this can be added to the doc's?

No, that approach has the same problem. Once the first rule deletes a 
record, it's gone from the view, so the second delete wouldn't know how 
to do it.

> So, the short answer is you can only perform one query
> in a rule, but you can have multiple rules defined to
> do what you need.

No, you can have multiple queries--you just have to understand that 
those that come first might have an effect on those that come later.

> Can you call a stored proc from a rule?  You could
> pass the to the stored proc and do as many
> queries as you like in there without worry that the
> would go away.

Yes, that would be one solution. Another would be to have an ON DELETE 
CASCADE on the foreign key constraint. Then, to delete the record from 
both tables, you just delete it from the primary key table.

> Just some thoughts.  It does suck that goes
> away.  Any way of preventing that from happening?

Doesn't sound like it. But your suggestion to use a function is a good 
one. (Although Tom did say something about volatile functions...).



In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2005-04-26 21:54:44
Subject: Re: How to make lazy VACUUM of one table run in several transactions ?
Previous:From: Rob ButlerDate: 2005-04-26 21:43:27
Subject: Re: DO INSTEAD and conditional rules

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