Re: [GENERAL] Are postgreSQL views UPDATEable?

Stuart Rison wrote:
>
> Hello,
>
> Found this snippet in a postgreSQL GENERAL posting:
>
> >Ross J. Reedstrom <reedstrm(at)rice(dot)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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005