Re: Bug in PL/pgSQL GET DIAGNOSTICS?

On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>OK, that is a good example.  It would return the sum of the matching
>tags.  You are suggesting here that it would be better to take the
>result of the last matching tag command, right?

The examples were meant to support my previous suggestion of
explicitly marking the statement you want to be counted, something
like:
CREATE VIEW twotables ASSELECT ... FROM table1 INNER JOIN table2 ON ... ;
CREATE RULE twotables_insert AS     -- INSERT ruleON INSERT TO twotables DO INSTEAD (    COUNT INSERT INTO table1
VALUES(new.pk, new.col1);    INSERT INTO table2 VALUES (new.pk, new.col2)); CREATE RULE twotables_update AS     --
UPDATEruleON UPDATE TO twotables DO INSTEAD (    COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;    UPDATE
table2SET col2 = new.col2 WHERE pk = old.pk); CREATE RULE twotables_delete AS     -- DELETE ruleON DELETE TO twotables
DOINSTEAD (    COUNT DELETE FROM table1 WHERE pk = old.pk;    DELETE FROM table2 WHERE pk = old.pk);
 
CREATE VIEW visible ASSELECT ... FROM table3WHERE deleted = 0;
CREATE RULE visible_delete AS     -- DELETE ruleON DELETE TO visible DO INSTEAD     COUNT UPDATE table3    SET deleted
=1    WHERE pk = old.pk;
 

One argument against automatically "don't count non-INSTEAD rules and
count the last statement in INSTEAD rules": sql-createrule.html says:
| for view updates: there must be an unconditional INSTEAD rule [...]
| If you want to handle all the useful cases in conditional rules, you
| can; just add an unconditional DO INSTEAD NOTHING rule [...]
| Then make the conditional rules non-INSTEAD
CREATE RULE v_update AS     -- UPDATE ruleON UPDATE TO v DO INSTEAD NOTHING;
CREATE RULE v_update2 AS     -- UPDATE ruleON UPDATE TO v WHERE <condition1>DO (    COUNT ...); 
CREATE RULE v_update3 AS     -- UPDATE ruleON UPDATE TO v WHERE <condition2>DO (    COUNT ...); 

ServusManfred