Обсуждение: UPDATE works on a view?

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

UPDATE works on a view?

От
Wells Oliver
Дата:
Err, sorry if this is back to basics, but I was very confused by someone being able to UPDATE myview SET col = 1 and the underlying table actually being updated properly.

Is this recent? When did we become able to UPDATE on views?

--

Re: UPDATE works on a view?

От
Tom Lane
Дата:
Wells Oliver <wells.oliver@gmail.com> writes:
> Err, sorry if this is back to basics, but I was very confused by someone
> being able to *UPDATE myview SET col = 1* and the underlying table actually
> being updated properly.
> Is this recent? When did we become able to UPDATE on views?

Depends on your definition of "recent" ...


Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [a99c42f29] 2012-12-08 18:26:21 -0500

    Support automatically-updatable views.
    
    This patch makes "simple" views automatically updatable, without the need
    to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
    are those classified as updatable according to SQL-92 rules.  The rewriter
    transforms INSERT/UPDATE/DELETE commands on such views directly into an
    equivalent command on the underlying table, which will generally have
    noticeably better performance than is possible with either triggers or
    user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
    continues to operate the same as before.
    
    For the moment, security_barrier views are not considered simple.
    Also, we do not support WITH CHECK OPTION.  These features may be
    added in future.
    
    Dean Rasheed, reviewed by Amit Kapila


            regards, tom lane



Re: UPDATE works on a view?

От
Wells Oliver
Дата:
Wild. Dead simple, had no idea, works. Learn something new every day.

Part of me thinks it's better for stuff to be explicit in what it is updating and views abstract that a little, but it's nice.

Thanks Tom!


On Wed, Aug 5, 2020 at 11:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Err, sorry if this is back to basics, but I was very confused by someone
> being able to *UPDATE myview SET col = 1* and the underlying table actually
> being updated properly.
> Is this recent? When did we become able to UPDATE on views?

Depends on your definition of "recent" ...


Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [a99c42f29] 2012-12-08 18:26:21 -0500

    Support automatically-updatable views.

    This patch makes "simple" views automatically updatable, without the need
    to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
    are those classified as updatable according to SQL-92 rules.  The rewriter
    transforms INSERT/UPDATE/DELETE commands on such views directly into an
    equivalent command on the underlying table, which will generally have
    noticeably better performance than is possible with either triggers or
    user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
    continues to operate the same as before.

    For the moment, security_barrier views are not considered simple.
    Also, we do not support WITH CHECK OPTION.  These features may be
    added in future.

    Dean Rasheed, reviewed by Amit Kapila


                        regards, tom lane


--

Re: UPDATE works on a view?

От
Paul Förster
Дата:
Hi Oliver,

> On 05. Aug, 2020, at 20:30, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Err, sorry if this is back to basics, but I was very confused by someone being able to UPDATE myview SET col = 1 and
theunderlying table actually being updated properly. 
>
> Is this recent? When did we become able to UPDATE on views?

yes, this is possible:

postgres=# create table t(i int);
CREATE TABLE
postgres=# create view v(i) as select i from t;
CREATE VIEW
postgres=# insert into v(i) values (0);
INSERT 0 1
postgres=# select * from v;
 i
---
 0
(1 row)

postgres=# update v set i=3;
UPDATE 1
postgres=# select * from v;
 i
---
 3
(1 row)

But I don't know since which PostgreSQL version views can up updated.

Cheers,
Paul


Re: UPDATE works on a view?

От
Magnus Hagander
Дата:


On Thu, Aug 6, 2020 at 7:56 AM Paul Förster <paul.foerster@gmail.com> wrote:
Hi Oliver,

> On 05. Aug, 2020, at 20:30, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Err, sorry if this is back to basics, but I was very confused by someone being able to UPDATE myview SET col = 1 and the underlying table actually being updated properly.
>
> Is this recent? When did we become able to UPDATE on views?

yes, this is possible:

postgres=# create table t(i int);
CREATE TABLE
postgres=# create view v(i) as select i from t;
CREATE VIEW
postgres=# insert into v(i) values (0);
INSERT 0 1
postgres=# select * from v;
 i
---
 0
(1 row)

postgres=# update v set i=3;
UPDATE 1
postgres=# select * from v;
 i
---
 3
(1 row)

But I don't know since which PostgreSQL version views can up updated.


For this type of high level features, the Feature Matrix is a pretty good way to find out these things (https://www.postgresql.org/about/featurematrix/#views-materialized-views).

Updatetable views was 9.3, WITH CHECK was 9.4. 


--

Re: UPDATE works on a view?

От
Paul Förster
Дата:
Hi Magnus,

> On 06. Aug, 2020, at 12:20, Magnus Hagander <magnus@hagander.net> wrote:
> For this type of high level features, the Feature Matrix is a pretty good way to find out these things
(https://www.postgresql.org/about/featurematrix/#views-materialized-views).

I didn't know that. Many thanks. :-)

Cheers,
Paul