Re: DO INSTEAD and conditional rules

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?

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