Postgres update rule returning number of rows affected

Posted by Lithium on Stack Overflow See other posts from Stack Overflow or by Lithium
Published on 2010-04-10T16:58:00Z Indexed on 2010/04/10 17:03 UTC
Read the original article Hit count: 412

Filed under:
|
|

I have a view table which is a union of two seperate 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 seperate 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.

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about sql