Re: DO INSTEAD and conditional rules - Mailing list pgsql-hackers

For this particular scenario, can't you just create
two  ON DELETE rules?  The first would delete from b,
the second from a.  Perhaps an example with a scenario
like this can be added to the doc's?

So, the short answer is you can only perform one query
in a rule, but you can have multiple rules defined to
do what you need.

Can you call a stored proc from a rule?  You could
pass the old.id to the stored proc and do as many
queries as you like in there without worry that the
old.id would go away.

Just some thoughts.  It does suck that old.id goes
away.  Any way of preventing that from happening?

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!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/