Re: DO INSTEAD and conditional rules - Mailing list 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@kineticode.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@Yahoo.com #