Обсуждение: update view

Поиск
Список
Период
Сортировка

update view

От
"Brian Haney"
Дата:
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
$

Re: [GENERAL] update view

От
Mike Mascari
Дата:
Brian Haney wrote:

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

It does, but you must supply the RULE for what should happen in the event of
an INSERT, UPDATE, or DELETE. See Chapter 35 in the Programmer's Guide - The
Postgres Rule System, the section entitled "Rules on INSERT, UPDATE and
DELETE".

Hope that helps,

Mike

Re: [GENERAL] update view

От
jose soares
Дата:
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@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
$

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