Re: [GENERAL] update view

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@cybernaut.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
$

************