Jan Wieck wrote:
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > ERROR: Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view. What do you think was happening on your old database
> when the "where old.status = 'appr'" clause wasn't satisfied?
> Nothing good I'm afraid. No harm in the UPDATE case, because so far there aren't any
tuples in the view that could be affected by the still
executed original query. But in an INSERT case, it would let
tuples through into the views heap file.
> If you really do need conditional rules, you can satisfy the check
> by writing one unconditional DO INSTEAD NOTHING rule and then one
> or more conditional non-INSTEAD rules. But you should think carefully
> about what you expect to happen when you use a conditional rule.
I'm using the view as a way of restricting a single class of users to only update tuples that have a certain status in the table. Isn't this essentially what a "dynamic view" is?
If someone happens to know the primary key of a record they should not be able to access, and they try to update it, I would like the backend to ignore the query (or better yet, raise an exception but I haven't figured out how to do that). If the status is correct, the update should proceed.
I've inserted the dummy do nothing rule as follows:
create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req;
create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead nothing;
create rule pay_req_v_prl_update as on update to pay_req_v_prl
where old.status = 'appr' do instead
update pay_req set status = new.status, gross = new.gross, cost = new.cost,
ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot
where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;
This seems to work now when I do:
psql ati -c "update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1;"
You see any problems with this method?
BTW, the update still returns UPDATE 0 from psql even though a record was updated. I've never quite figured out why views with rules do this.
I've also done some testing on 7.1 for that nasty thing in 7.0 where you had to give select,update privs to a table referenced by a foreign key. So far, looks good. I was able to reference a table that the user didn't have privs to at all. I think that is the desired behavior.
Good work guys! 7.1 is looking good.
