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: c3559a5c2aff0029ac9bdae7e801895c@kineticode.com (view raw or flat)
Thread:
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 old.id to the stored proc and do as many
> queries as you like in there without worry that the
> old.id 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 old.id 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...).

Regards,

David


In response to

Responses

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-2014 The PostgreSQL Global Development Group