Re: Rule not invoked in 7.1

Tom Lane wrote:

> Kyle <kyle(at)actarg(dot)com> writes:
> > 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.
>
> This might be better done with a trigger than a rule. For one thing,
> a trigger can easily raise an exception. MHO is that rules are good
> when you need to update multiple rows in other tables when certain
> things happen. If you just want to validate or twiddle an individual
> tuple as it's inserted/updated, a trigger is a good bet.
>

I have another interesting use of this same concept you may be interested in
(or slightly nausious, as the case may be):

The underlying database (empl) contains all employees in the organization. The
view empl_v_sup calls a recursive function to determine if
a given employee works for the current user (either directly, or anywhere under
him in the company heirarchy). The view only includes
employees that work under the that user. There is also an exception for users
who have certain types of privileges who get to see the whole
company.

This dynamic view is very cool as it allows different people to see different
data in the same view depending on who they are, and how the
hierarchical data is arranged in the employee database.

-- Determine if an employee has another employee as his supervisor.
-- An employee is, by definition, not his own supervisor
-- Returns true or false
-- calling sequence: _empl_ancest(employee,ancestor,level)
create function _empl_ancest(int4,int4,int4) returns boolean as '
declare
trec record;
begin
if $3 > 15 then
raise exception \'Supervisor loop found on employee %\', $1;
end if;

-- a person can not be his own supervisor
-- also if null or 0, we reached top of the ladder so return false
if $1 = $2 or $1 is null or $1 = 0 then return false; end if;

-- get the employees record
select * into trec from empl_pub where empl_id = $1;
if not found then
raise exception \'Record not found for employee %\', $1;
end if;

-- if he is his own supervisor, we have probably reached the top so false
if trec.superv = $1 then return false; end if;

-- if his supervisor is the ancestor, return true
if trec.superv = $2 then return true; end if;

-- else check the parent recursively
return _empl_ancest(trec.superv, $2, $3+1);
end;' language 'plpgsql';

-- Determine if an employee has another employee as his ancestor.
-- This adds a level parm to prevent infinite recursion.
-- calling sequence: empl_ancest(employee,ancestor)
create function empl_ancest(int4,int4) returns boolean as '
select _empl_ancest($1,$2,0);
' language 'sql';

--View with limited privileges for supervisors to see their own people
create view empl_v_sup as select *,oid as _oid from empl where
exists (select * from priv where empl_id = getpguid() and ((priv = 'emplim'
and alevel = 'super') or (priv = 'payroll'))) or
empl_ancest(empl_id,getpguid());

--Only the emplim-super can insert records
create rule empl_v_sup_innull as on insert to empl_v_sup do instead nothing;
create rule empl_v_sup_insert as on insert to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'super' and
empl_id = getpguid()) > 0
do instead
insert into empl
(empl_id,pertitle,surname,givnames,prefname,jobtitle,addr,city,state,zip,country,phone,workph,mobile,email,ssn,bday,hiredate,termdate,lrevdate,nrevdate,paytyp,empltyp,superv,proxy,status,mstat,payrate,allow,wccode,eic,cmt)

values
(new.empl_id,new.pertitle,new.surname,new.givnames,new.prefname,new.jobtitle,new.addr,new.city,new.state,new.zip,new.country,new.phone,new.workph,new.mobile,new.email,new.ssn,new.bday,new.hiredate,new.termdate,new.lrevdate,new.nrevdate,new.paytyp,new.empltyp,new.superv,new.proxy,new.status,new.mstat,new.payrate,new.allow,new.wccode,new.eic,new.cmt);

--Emplim-super can update any field
create rule empl_v_sup_upnull as on update to empl_v_sup do instead nothing;
create rule empl_v_sup_update as on update to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'super' and
empl_id = getpguid()) > 0
do instead
update empl set empl_id = new.empl_id, pertitle = new.pertitle, surname =
new.surname, givnames = new.givnames, prefname = new.prefname, jobtitle =
new.jobtitle, addr = new.addr, city = new.city, state = new.state, zip =
new.zip, country = new.country, phone = new.phone, workph = new.workph, mobile
= new.mobile, email = new.email, ssn = new.ssn, bday = new.bday, hiredate =
new.hiredate, termdate = new.termdate, lrevdate = new.lrevdate, nrevdate =
new.nrevdate, paytyp = new.paytyp, empltyp
where empl_id = old.empl_id;

--Emplim-user can update certain fields
create rule empl_v_sup_update1 as on update to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'user' and
empl_id = getpguid()) > 0 and
empl_ancest(old.empl_id,getpguid())
do instead
update empl set pertitle = new.pertitle, prefname = new.prefname, jobtitle
= new.jobtitle, phone = new.phone, workph = new.workph, mobile = new.mobile,
email = new.email, lrevdate = new.lrevdate, nrevdate = new.nrevdate, payrate =
new.payrate
where empl_id = old.empl_id;