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(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