I would like to use an UPDATE RULE to modify the action performed
when any UPDATE is attempted on a certain table,
*including* an UPDATE which would fail because of no rows matching the WHERE.
Is this at all possible? I have tried with variations of ALSO|INSTEAD etc
but the RULE is never invoked in the failing case. And my reading of
chapter 38.3.1. How Update Rules Work
is that the original query's quallification is always present.
Example :
create table updatable (id bigint , version int , discriminator text)
insert into updatable values (1 , 0 , 'root')
insert into updatable values (2 , 0 , 'leaf')
select * from updatable order by id
id | version | discriminator
----+---------+---------------
1 | 0 | root
2 | 0 | leaf
(2 rows)
CREATE or REPLACE FUNCTION optlock_control( OLD public.updatable , NEW public.updatable )
returns bool LANGUAGE c AS '\$libdir/optlock_control.so', 'optlock_control'
/* for this little test, this function always inserts one row with a high id into the table update */
CREATE OR REPLACE RULE update_updatable AS ON UPDATE to updatable
DO INSTEAD SELECT optlock_control(OLD,NEW)
Now I want optlock_control() invoked on every UPDATE of updatable.
try it with an UPDATE that would have succeeded:
update updatable set version = 1 where id = 2 and version = 0
optlock_control
-----------------
f
(1 row)
/* verify function was invoked */
select * from updatable order by id
id | version | discriminator
-----+---------+---------------
1 | 0 | root
2 | 0 | leaf
999 | 0 | 9 /* yes it was */
(3 rows)
Now try it with an UPDATE that would have failed:
update updatable set version = 2 where id = 1 and version = 1
optlock_control
-----------------
(0 rows)
UPDATE 0
/* was my function invoked ? */
select * from updatable order by id"
id | version | discriminator
-----+---------+---------------
1 | 0 | root
2 | 0 | leaf
999 | 0 | 9
(3 rows)
/* no it wasnt */