Reputation: 5142

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:

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.

Upvotes: 2

Answers (3)

user2059857

Reputation: 1401

As documented in Section 39.6. Rules and Command Status

If there is any unconditional INSTEAD rule for the query, then the original query will not be executed at all. In this case, the server will return the command status for the last query that was inserted by an INSTEAD rule (conditional or unconditional) and is of the same command type (INSERT, UPDATE, or DELETE) as the original query. If no query meeting those requirements is added by any rule, then the returned command status shows the original query type and zeroes for the row-count and OID fields.

In my case I was able to work around by exposing the relation using a view and a INSTEAD OF trigger on the view.

Upvotes: 0