Re: [GENERAL] update view

You must create a rule to make a view updatable as in the following example:

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

insert into emp values (7499,'ALLEN', 'SALESMAN', '20-FEB-81', '$1600', 300,
20,
insert into emp values (7698,'BLAKE', 'MANAGER', '01-MAY-81', '$2850',NULL,
30,
insert into emp values (7900,'JONES', 'CLERK', '03-DEC-81', '$0950',NULL,
30,
insert into emp values (7901,'KING', 'SALESMAN', '03-DEC-81', '$1950',NULL,
30,

drop view vista;
create view vista
as select empno, ename, job
from emp
where job='SALESMAN';

create rule "_UPDvista" as on update to vista
WHERE new.job='SALESMAN'
do instead
update emp set empno=new.empno, ename=new.ename,job=new.job
where empno=OLD.empno;

select * from vista;

empno|ename |job
-----+--------------------+------------
7499|ALLEN |SALESMAN
7901|KING |SALESMAN
(2 rows)

update vista set empno=1 WHERE ename='ALLEN';

select * from vista;
empno|ename |job
-----+--------------------+------------
7901|KING |SALESMAN
1|ALLEN |SALESMAN
(2 rows)

José

Brian Haney ha scritto:

> I'm trying to update a table through a view and have read up on what
> constitutes an 'updatable' view. I created a simple test case and cannot
> get it to update the table through the view. In the transcript below,
> notice that when I update the view, I get 'UPDATE 0' with no error message
> or other complaints. What am I missing? Does PostgreSQL not support
> updating through views?
>
> -- Brian Haney
> brian(at)cybernaut(dot)com
>
> ============================ BEGIN SAMPLE ========================
>
> $ psql -f /tmp/viewtest test2
> create table peanuts (
> name text,
> age int4,
> height int4,
> weight int4);
> CREATE
> insert into peanuts values ('Charlie Brown', 50, 24, 75);
> INSERT 21228 1
> insert into peanuts values ('Snoopy', 21, 18, 25);
> INSERT 21229 1
> insert into peanuts values ('Lucy van Pelt', 50, 27, 65);
> INSERT 21230 1
> insert into peanuts values ('Linus van Pelt', 50, 24, 75);
> INSERT 21231 1
> select * from peanuts;
> name |age|height|weight
> --------------+---+------+------
> Charlie Brown | 50| 24| 75
> Snoopy | 21| 18| 25
> Lucy van Pelt | 50| 27| 65
> Linus van Pelt| 50| 24| 75
> (4 rows)
>
> create view dogs as select * from peanuts where name = 'Snoopy';
> CREATE
> select * from dogs;
> name |age|height|weight
> ------+---+------+------
> Snoopy| 21| 18| 25
> (1 row)
>
> update dogs set age = 145;
> UPDATE 0
> select * from dogs;
> name |age|height|weight
> ------+---+------+------
> Snoopy| 21| 18| 25
> (1 row)
>
> EOF
> $
>
> ************