Have Geek Brain

Postgres update rule returning the number of rows affected

I have a view table that is a concatenation of two separate tables (like Table_A and Table_B).

I needed to be able to update a row in a view table and it seems the way to do this was via a "view rule". All records in the view table have a separate identifier, so an identifier that exists in table _A will not 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 affected rows (1). However, if I update table _A, it returns (0) rows, even if the data has changed. If I change the order of the updates, the same thing happens, but vice versa.

How can I resolve this so that it returns the correct number of affected rows.

Thanks.

+2


source to share


3 answers


You can not. Unless you do this in a stored procedure and return the number of rows affected.



Nice explanation in the official doc

+1


source


It is right. PostgreSQL cannot know using a rule. Also, the rules are slow. You should really consider a trigger instead.



0


source


As described in Section 39.6. Team rules and status

If any unconditional INSTEAD rule exists for the request, the original request will not be executed at all. In this case, the server will return the command status for the last request that was inserted into the INSTEAD rule (conditional or unconditional) and of the same command type (INSERT, UPDATE, or DELETE) as the original request. If no query satisfying these requirements is added by any rule, then the command status returned shows the original query type and zeros for the count and OID fields.

In my case, I was able to get INSTEAD OF

around by exposing the relationship using a view and a trigger on the view.

0


source






More articles:


All Articles