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'-
