Re: [HACKERS] about RULES

Jose' Soares ha scritto:

> >
> > In the case of a qualified instead rule, the parsetree get's
> > splitted. One with the qual, one with the negated qual. If
> > you only want 'SALESMAN's, you must put the qualification
> > into the INSERT action of the rule as
> >
> > create rule "_INSvista" as on insert to vista
> > do instead
> > insert into emp select new.empno, new.ename, new.job
> > where new.job='SALESMAN';
>
> I see that rules is more complex than I thougth.
>
> I tried your example Jan but it doesn't work...
>
> create rule "_INSvista" as on insert to vista
> do instead
> insert into emp select new.empno, new.ename, new.job
> where new.job='SALESMAN';
> ERROR: Type of empno does not match target column ename
>
> -Jose'-

I had no reply to this message probably because a problem with my mail.

I'm trying to make a view updatable using triggers, INSERT works fine
but UPDATE/DELETE
doesn't because the WHERE condition is evaluated FALSE all the time.
Here an example:

create table emp (
empno int,
ename char(10),
job char(12),
hiredate date,
sal money,
comm int,
deptno int,
level int,
mgr int
);
CREATE

insert into emp values (8900,'MANUEL','SALESMAN',CURRENT_DATE,'$2,000');

INSERT 149844 1

create table vista
as select empno, ename, job
from emp
where job='SALESMAN';
CREATE

drop function add_vista();
create function add_vista() returns opaque as '
begin
raise notice ''trigger fired: % on %'',tg_when,tg_op;
return null;
end;
' language 'plpgsql';

create trigger t_add_vista before insert or update or delete
on vista for each row execute procedure add_vista();

delete from vista where ename='MANUEL';
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0
delete from vista ;
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0

--And now VISTA becames a view:.................

CREATE RULE "_RETvista" AS
ON SELECT TO "vista"
DO INSTEAD
SELECT "empno", "ename", "job"
FROM "emp" WHERE "job" = 'SALESMAN'::"bpchar";
CREATE

select * from vista;

empno|ename |job
-----+----------+------------
8900|MANUEL |SALESMAN
(1 row)

-- The where condition is all the time evaluated as FALSE...

delete from vista where ename='MANUEL'; --why this condition isn't true
?
DELETE 0

delete from vista ;
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0

--Is there a way to make views updatable ?

-Jose'-