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
[EMAIL PROTECTED]============================ 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
$************
