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

Re: DO INSTEAD and conditional rules

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Rob Butler <crodster2k(at)Yahoo(dot)com>
Cc: David Wheeler <david(at)kineticode(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>,Neil Conway <neilc(at)samurai(dot)com>
Subject: Re: DO INSTEAD and conditional rules
Date: 2005-04-26 19:14:14
Message-ID: 426E9306.6090702@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 4/26/2005 3:01 PM, Rob Butler wrote:

> Are rules even needed anymore?  Can't you do this all
> with triggers?  If you want to "DO INSTEAD" just use a
> row based trigger, and return null.  Or is this less
> efficient?

On INSERT, yes, on UPDATE, how so?


Jan

> 
> Later
> Rob
> --- David Wheeler <david(at)kineticode(dot)com> wrote:
>> On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
>> 
>> > Well, they handle simple situations OK, but we
>> keep seeing people get
>> > burnt as soon as they venture into interesting
>> territory.  For 
>> > instance,
>> > if the view is a join, you can't easily make a
>> rule that turns a delete
>> > into deletions of both joined rows.  And you'll
>> get burnt if you try to
>> > insert any volatile functions, because of the
>> multiple-evaluation 
>> > issue.
>> > Etc.
>> 
>> sharky=# CREATE TABLE a (
>> sharky(#   id int,
>> sharky(#   name text
>> sharky(# );
>> CREATE TABLE
>> sharky=# CREATE TABLE b (
>> sharky(#   a_id int,
>> sharky(#   rank text
>> sharky(# );
>> CREATE TABLE
>> sharky=#
>> sharky=# CREATE VIEW ab AS
>> sharky-#   SELECT id, name, rank
>> sharky-#   FROM   a, b
>> sharky-#   WHERE  a.id = b.a_id
>> sharky-# ;
>> CREATE VIEW
>> sharky=# CREATE RULE delete_ab AS
>> sharky-# ON DELETE TO ab DO INSTEAD (
>> sharky(#   DELETE FROM b
>> sharky(#   WHERE  a_id = OLD.id;
>> sharky(#
>> sharky(#   DELETE FROM a
>> sharky(#   WHERE  id = OLD.id;
>> sharky(# );
>> CREATE RULE
>> sharky=#
>> sharky=#
>> sharky=# insert into a values (1, 'test');
>> INSERT 597795 1
>> sharky=# insert into b values (1, 'sergeant');
>> INSERT 597796 1
>> sharky=# select * from ab;
>>   id | name |   rank
>> ----+------+----------
>>    1 | test | sergeant
>> (1 row)
>> 
>> sharky=# delete from ab;
>> DELETE 0
>> sharky=# select * from ab;
>>   id | name | rank
>> ----+------+------
>> (0 rows)
>> 
>> sharky=# select * from a;
>>   id | name
>> ----+------
>>    1 | test
>> (1 row)
>> 
>> sharky=# select * from b;
>>   a_id | rank
>> ------+------
>> (0 rows)
>> 
>> Ah, yes, you're right, that is...unexpected. Perhaps
>> OLD can contain 
>> its values for the duration of the RULE's
>> statements? I'm assuming that 
>> what's happening is that OLD.id is NULL after the
>> first of the two 
>> DELETE statements...
>> 
>> > Like I said, I don't have a better idea.  Just a
>> vague feeling of
>> > dissatisfaction.
>> 
>> I'd call it a bug. ;-)
>> 
>> Regards,
>> 
>> David
>> 
>> 
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>> 
>>                http://archives.postgresql.org
>> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 


-- 
#======================================================================#
# 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 #

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-04-26 19:35:32
Subject: Re: DO INSTEAD and conditional rules
Previous:From: Rob ButlerDate: 2005-04-26 19:01:06
Subject: Re: DO INSTEAD and conditional rules

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