Re: DO INSTEAD and conditional rules

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 #