Re: RULE with conditional behaviour? - Mailing list pgsql-general

I wrote:
> If the above approach seems too complex, another possibility is to write
> the rule attached to the view as something simple that invokes an action
> you know will fire a trigger.  For example,
> ON UPDATE TO view DO INSTEAD INSERT INTO base-table VALUES(needed-values)

This approach may not work well if your view is a join and so you might
need to update multiple base tables.  There is a sneaky way to get
around that, which is to use a dummy table that has no other purpose
than to be a place where you can fire a trigger.  For example,

ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT NEW.*

Here, dummy-table is a real table (not a view), but it will never have
any rows in it, because you'll give it an on-insert trigger that always
suppresses the insert.  Now, that trigger has access to a full row of
the "updated view" and so it can go off and do whatever is needed to
the underlying base tables.

If you did it just like this then you'd actually need three dummy
tables, one each for insert, update, and delete actions on the view.
If that seems like overkill, consider

ON INSERT TO view DO INSTEAD INSERT INTO dummy-table SELECT 1, NEW.*
ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT 2, NEW.*
ON DELETE TO view DO INSTEAD INSERT INTO dummy-table SELECT 3, OLD.*

Now the insert trigger can look at the first column to decide what to
do.  (Note you can't do anything useful with update or delete triggers
on the dummy table; it'll never have any rows so they'd never fire.)
In general, the columns of the dummy table are exactly the parameter
list you need to pass to your trigger, and so you can make 'em whatever
you like.  For example, to correctly update a view you might need both
the old and new states of the (virtual) view row.  No problem:

ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT OLD.*, NEW.*

where dummy-table is declared with the necessary column set.

I have not actually had occasion to use this approach myself, but
here is a simple example showing that it would work:

regression=# select * from b1;
     f1      |     f2
-------------+-------------
           0 |           0
      123456 |       61728
     -123456 |      -61728
  2147483647 |  1073741823
 -2147483647 | -1073741823
(5 rows)

regression=# select * from b2;
     f1      |     f2
-------------+-------------
           0 |           2
      123456 |      123458
     -123456 |     -123454
  2147483647 | -2147483647
 -2147483647 | -2147483645
(5 rows)

regression=# create view vvv as
regression-# select b1.f1, b1.f2, b2.f2 as f22 from b1 join b2 using (f1);
CREATE
regression=# select * from vvv;
     f1      |     f2      |     f22
-------------+-------------+-------------
 -2147483647 | -1073741823 | -2147483645
     -123456 |      -61728 |     -123454
           0 |           0 |           2
      123456 |       61728 |      123458
  2147483647 |  1073741823 | -2147483647
(5 rows)

-- Note that this update makes no sense in terms of either base table alone:
regression=# update vvv set f22 = 43 where f2 =  61728 ;
ERROR:  Cannot update a view without an appropriate rule

regression=# create table vvv_dummy (f1 int, f2 int, f22 int,
regression(# new_f1 int, new_f2 int, new_f22 int);
CREATE
regression=# create rule vvv_update as on update to vvv do instead
regression-# insert into vvv_dummy select old.*, new.*;
CREATE

regression=# update vvv set f22 = 43 where f2 =  61728 ;
UPDATE 0
regression=# select * from vvv_dummy;
   f1   |  f2   |  f22   | new_f1 | new_f2 | new_f22
--------+-------+--------+--------+--------+---------
 123456 | 61728 | 123458 | 123456 |  61728 |      43
(1 row)

I didn't bother to make an ON INSERT trigger for vvv_dummy, but if I had
one, it would have received the data shown here as inserted into vvv_dummy.

In short: by using a dummy table you can get the effect of a trigger
applied to a view.

            regards, tom lane