Lithium Lithium - 1 year ago 93
SQL Question

Postgres update rule returning number of rows affected

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:

ON UPDATE TO viewtable DO INSTEAD ( UPDATE _A SET foo = false
UPDATE _B SET foo = false

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.


Answer Source

You can't. Unless you do it in a stored procedure and return the number of affected rows.

A good explanation in the official doc