The RULE infrastructure permits the programmer to specify a
series of commands in the DO action
from the syntax diagram in the manual :
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ;
command ... ) }
The manual described the series of commands as the " rule action" , implying (to me) that there is a sense in which the entire series comprising the action is one operation.
I am specifically interested in the case of update and an example
of an unconditional rule such as
CREATE or REPLACE RULE multi-action AS ON UPDATE TO my_view
DO INSTEAD (
UPDATE my_table_a
SET a_column = value
WHERE OLD.keycolumn = keyvalue;
UPDATE my_table_b
SET b_column = value
WHERE OLD.keycolumn = keyvalue;
);
where my intention is that one and only one of the action commands
should update any row.
This all works except for one thing : the final status, including the (for me) all-important number of rows updated.
It turns out that in this example, if the UPDATE my_table_b updates one (or more) rows, the status shows that number, but if the UPDATE my_table_b updates no rows, the status shows 0 rows updated, even if one (or more) rows of my_table_a were successfully updated by the first command. This is not what I want.
The chapter entitled "Rules and Command Status " (approximately chap number 41.6 depending on version) says
"If
there is any unconditional INSTEAD rule
for the query, then the original query will not be executed at
all. In this case, the server will return the command status for
the last query that was inserted by an INSTEAD rule
(conditional or unconditional) and is of the same command type (INSERT, UPDATE,
or DELETE)
as the original query." (my bold of the word query).
But
what is a query in this context? In my example, is the
last query the
. action of the last unconditional RULE which
executed (only one in my example but there could be other
applicable rules for update of my_view)
OR
. last command of the series of commands comprising
the
action of the last unconditional RULE which executed
?
Well, I assume what postgresql actually does is the latter, but surely there is a case for it to be the former, where the rows_updated of the action would be the sum of all rows updated by all commands in that action's series. . In my example, postgresql is telling the application that no rows were updated when actually one (or more) row was updated, and the sum of all rows updated is one.
Any
thoughts? Any rationales one way or the other? Any
interest in perhaps providing a choice via a configuration
parameter?
Cheers,
John Lumby
