I have a view table which is a union of two separate tables (say Table _A and Table _B).
I need to be able to update a row in the view table, and it seems the way to do this was through a 'view rule'. All entries in the view table have separate id's, so an id that exists in table _A won't exist in table _B.
I created the following rule:
CREATE OR REPLACE RULE view_update AS
ON UPDATE TO viewtable DO INSTEAD ( UPDATE _A SET foo = false
WHERE old.id = _A.id;
UPDATE _B SET foo = false
WHERE old.id = _B.id;
);
If I do an update on table _B it returns the correct number of rows affected (1). However if I update table _A it returns (0) rows affected even though the data was changed. If I swap out the order of the updates then the same thing happens, but in reverse.
How can I solve this problem so that it returns the correct number of rows affected.
Thanks.