Letting users update views

Solution

Add rules to the view to redirect changes to the underlying table(s). Remember that Postgres won't allow updates/inserts unless there is an unqualified rule that catches all changes ref-view ref-rule.

Discussion

To let users update a view you need to provide appropriate rules to Postgres with the CREATE RULE construct. In the example of 6.1 we want the accounts department to be able to alter orders but not delete them (goods have been shipped but not necessarily paid for).

CREATE RULE accts_orders_upd_rule0 AS ON UPDATE TO accts_orders
DO INSTEAD NOTHING;

CREATE RULE accts_orders_upd_rule AS ON UPDATE TO accts_orders
WHERE NEW.ord_paid != OLD.ord_paid OR OLD.ord_paid IS NULL
DO INSTEAD
UPDATE orders SET ord_paid=NEW.ord_paid WHERE ord_id=NEW.ord_id;
   

The first rule looks simple enough - instead of updating accts_orders do nothing instead. Why do we need it, since you can't update rules anyway? Postgres won't try to update a view unless there is a rule that covers all eventualities. Since the second rule has a WHERE clause we need an empty rule to cover all possible updates a user can attempt.

This second rule captures updates to the accts_orders view and translates them into update queries onto accts_orders. In addition it only updates the ord_paid field, ignoring others. The NEW keyword represents the values the record is going to be updated with. The OLD keyword represents the record before the update takes place.

What about permissions? The rule is executed with the permissions of its owner not the person who ends up executing it. So - if accounts don't have the permissions to access the orders table that's alright, as long as the user who creates the rule does.