Return of INSTEAD rules - Mailing list pgsql-hackers

We have talked about possible return values for RULES, particularly
INSTEAD rule.  Manfred has a nice example here, so I propose we handle
INSTEAD rules this way:  that we return the oid and tuple count of the
last INSTEAD rule query with a tag matching the main query.  The
returned tag, of course, would be the tag of the main query.  This works
for Manfred's case, and it works for my case when there is only one
action in the INSTEAD rule.  If there is more than one matching tag in
the INSTEAD rule, the user has the option to place the query he wants
for the return at the end of the rule.  This does give the user some
control over what is returned.

Comments?

I think non-INSTEAD rules already return the tag, oid, and tuple count of
the main query, right?

---------------------------------------------------------------------------

Manfred Koizar wrote:
> 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 AS
>     SELECT ... FROM table1 INNER JOIN table2 ON ... ;
> 
>     CREATE RULE twotables_insert AS     -- INSERT rule
>     ON 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     -- UPDATE rule
>     ON UPDATE TO twotables 
>     DO INSTEAD (
>         COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
>         UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
>     ); 
>     
>     CREATE RULE twotables_delete AS     -- DELETE rule
>     ON DELETE TO twotables 
>     DO INSTEAD (
>         COUNT DELETE FROM table1 WHERE pk = old.pk;
>         DELETE FROM table2 WHERE pk = old.pk
>     );
> 
>     CREATE VIEW visible AS
>     SELECT ... FROM table3
>     WHERE deleted = 0;
> 
>     CREATE RULE visible_delete AS     -- DELETE rule
>     ON 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 rule
>     ON UPDATE TO v 
>     DO INSTEAD NOTHING;
> 
>     CREATE RULE v_update2 AS     -- UPDATE rule
>     ON UPDATE TO v WHERE <condition1>
>     DO (
>         COUNT ...
>     ); 
> 
>     CREATE RULE v_update3 AS     -- UPDATE rule
>     ON UPDATE TO v WHERE <condition2>
>     DO (
>         COUNT ...
>     ); 
> 
> Servus
>  Manfred
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073