pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE works on a view?
6+ messages / 4 participants
[nested] [flat]
* UPDATE works on a view?
@ 2020-08-05 18:30 Wells Oliver <wells.oliver@gmail.com>
2020-08-05 18:42 ` Re: UPDATE works on a view? Tom Lane <tgl@sss.pgh.pa.us>
2020-08-06 05:56 ` Re: UPDATE works on a view? Paul Förster <paul.foerster@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Wells Oliver @ 2020-08-05 18:30 UTC (permalink / raw)
To: pgsql-admin
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?
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE works on a view?
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
@ 2020-08-05 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-05 18:44 ` Re: UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-08-05 18:42 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE works on a view?
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
2020-08-05 18:42 ` Re: UPDATE works on a view? Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-05 18:44 ` Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Wells Oliver @ 2020-08-05 18:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE works on a view?
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
@ 2020-08-06 05:56 ` Paul Förster <paul.foerster@gmail.com>
2020-08-06 10:20 ` Re: UPDATE works on a view? Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 6+ messages in thread
From: Paul Förster @ 2020-08-06 05:56 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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.
Cheers,
Paul
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE works on a view?
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
2020-08-06 05:56 ` Re: UPDATE works on a view? Paul Förster <paul.foerster@gmail.com>
@ 2020-08-06 10:20 ` Magnus Hagander <magnus@hagander.net>
2020-08-06 10:30 ` Re: UPDATE works on a view? Paul Förster <paul.foerster@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Hagander @ 2020-08-06 10:20 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE works on a view?
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
2020-08-06 05:56 ` Re: UPDATE works on a view? Paul Förster <paul.foerster@gmail.com>
2020-08-06 10:20 ` Re: UPDATE works on a view? Magnus Hagander <magnus@hagander.net>
@ 2020-08-06 10:30 ` Paul Förster <paul.foerster@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Paul Förster @ 2020-08-06 10:30 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-08-06 10:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-05 18:30 UPDATE works on a view? Wells Oliver <wells.oliver@gmail.com>
2020-08-05 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-05 18:44 ` Wells Oliver <wells.oliver@gmail.com>
2020-08-06 05:56 ` Paul Förster <paul.foerster@gmail.com>
2020-08-06 10:20 ` Magnus Hagander <magnus@hagander.net>
2020-08-06 10:30 ` Paul Förster <paul.foerster@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox