Обсуждение: Are postgreSQL views UPDATEable?

Hello,

Found this snippet in a postgreSQL GENERAL posting:

>Ross J. Reedstrom <reedstrm@rice.edu> wrote
>
>Eric -
>I've redirected your question to the general list, since it seems to fit
>in there better than the sql list.  I think you're confusing mySQL's
>limitations with those of PostgreSQL (PG from here on) - PG does in fact
>support views, has for quite a while. They're even updateable.
                                                    ^^^^^^^^^^^
What exactly does this mean?

Does you can INSERT into a view with tuples being inserted into the base
table(s) accordingly?

Does it mean you can UPDATE values in the views and values in the base
table(s) will be accordingly modified?

I have tried both and have not got either of these to work, even on single
base tables views.

I'm running PG 6.4.0.

regards,

Stuart.


+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

Stuart Rison wrote:
>
> Hello,
>
> Found this snippet in a postgreSQL GENERAL posting:
>
> >Ross J. Reedstrom <reedstrm@rice.edu> wrote
> >
> >[PostGreSQL does] support views, has for quite a while. They're even updateable.
>                                                     ^^^^^^^^^^^
> What exactly does this mean?
>
> Does you can INSERT into a view with tuples being inserted into the base
> table(s) accordingly?
>

Yes, but it's not automatic. To quote Jan Wieck from a mail on the
HACKERS list:

<JW> > When will updateable views be supported?
<JW>
<JW>     Since v6.4 :-)
<JW>
<JW>     Look  at  the  rule  system  documentation in the programmers
<JW>     manual for details.

http://www.postgresql.org/docs/programmer/rules631.htm

<Stuart>
> Does it mean you can UPDATE values in the views and values in the base
> table(s) will be accordingly modified?
>

The problem is with the 'accordingly': since a view can be any select
statement, the reverse mapping is not easily computed, automatically.
So, you must define them yourself.

Essentially, VIEWs in PostgreSQL are just tables with a ON SELECT rule
that does a INSTEAD SELECT. With v6.4, the RULE system was expanded to
allow for ON INSERT, UPDATE or DELETE,
so you may write rules to handle updating VIEWs.

The docs mentioned above are actually very good at describing all this.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005