Thread: RV: A little problem updating data with views

Hello,
   We are using PostgreSQL v6.4 and have problems when we want update   data throught views.   When we issue an update
statmentwithin a view, always obtain 'UPDATE 0'   and the data is not updated. We have checked write permissions on
view  and are right.
 
   Any ideas was going wrong?
   thanks in advance.


A complete UPDATE test:

test=> create table ttest (t varchar(20), n int4);
test=> create view vtest as select t from ttest;
test=> insert into ttest values('asd', 0);
test=> select * from vtest;
t
---
asd
(1 row)

test=> grant all on ttest, vtest to postgres;
CHANGE

test=> update vtest set t='asdasd';
UPDATE 0

I don't believe views are updatable in postgresql.

Jorge Herrera Piñero wrote:
> 
> Hello,
> 
>     We are using PostgreSQL v6.4 and have problems when we want update
>     data throught views.
>     When we issue an update statment within a view, always obtain 'UPDATE 0'
>     and the data is not updated. We have checked write permissions on view
>     and are right.
> 
>     Any ideas was going wrong?
> 
>     thanks in advance.
> 
> A complete UPDATE test:
> 
> test=> create table ttest (t varchar(20), n int4);
> test=> create view vtest as select t from ttest;
> test=> insert into ttest values('asd', 0);
> test=> select * from vtest;
> t
> ---
> asd
> (1 row)
> 
> test=> grant all on ttest, vtest to postgres;
> CHANGE
> 
> test=> update vtest set t='asdasd';
> UPDATE 0

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

"Jorge Herrera Piñero" <jhpinero@ull.es> writes:
>     When we issue an update statment within a view, always obtain 'UPDATE 0'
>     and the data is not updated. We have checked write permissions on view
>     and are right.

I learned this the hard way a few weeks ago.  A view is just a table
that has an "on select" rule that rewrites select queries to do
something else than pull tuples from the view table itself.

If you want to insert into a view, you need to write an "on insert"
rule that does something appropriate with the tuples.  (Else they'll
get dumped into the underlying table, where you'll never see them
again because you can't select them --- the "on select" rule will
redirect your select queries.)

Likewise, updating a view isn't going to do anything useful unless
you provide a rule to define what it should mean (else the update is
applied to the empty table that underlies the view...)

The system will not provide these rules for you because in the
general case there's no way to automatically decide what to do
(if the view is made by a complicated select, there may not *be*
any real tuples that could be updated...)  But if you can figure
out a reasonable interpretation for modifications to the view,
you can write a rule that does it.

I think in 6.6, Jan is going to provide default insert/update rules for
views that will report an error, so that you're not left wondering what
the heck happened to your query.

Anyway, the bottom line is to go read up on query-rewrite rules.
        regards, tom lane

>
> I don't believe views are updatable in postgresql.

    They are - if someone read the chapter on rules.

    CREATE RULE vtest_upd AS ON UPDATE TO VTEST DO INSTEAD
        UPDATE ttest SET t = new.t WHERE t = old.t;

    It is just that those rules cannot simply be generated on the
    fly while CREATE VIEW. In this simple case of course, but  if
    the  view is a join of 3 tables, it's not clear how to update
    them (if at all).  So it's up to the DB  designer  to  create
    them by hand.


Jan

>
> Jorge Herrera Pi=F1ero wrote:
> >=20
> > Hello,
> >=20
> >     We are using PostgreSQL v6.4 and have problems when we want update
> >     data throught views.
> >     When we issue an update statment within a view, always obtain 'UPDA=
> TE 0'
> >     and the data is not updated. We have checked write permissions on v=
> iew
> >     and are right.
> >=20
> >     Any ideas was going wrong?
> >=20
> >     thanks in advance.
> >=20
> > A complete UPDATE test:
> >=20
> > test=3D> create table ttest (t varchar(20), n int4);
> > test=3D> create view vtest as select t from ttest;
> > test=3D> insert into ttest values('asd', 0);
> > test=3D> select * from vtest;
> > t
> > ---
> > asd
> > (1 row)
> >=20
> > test=3D> grant all on ttest, vtest to postgres;
> > CHANGE
> >=20
> > test=3D> update vtest set t=3D'asdasd';
> > UPDATE 0
>
> --=20
> Chris Bitmead
> http://www.bigfoot.com/~chris.bitmead
> mailto:chris.bitmead@bigfoot.com
>
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #