Re: PostgreSQL Gotchas

On Sat, Oct 08, 2005 at 06:05:29PM +0400, Nikolay Samokhvalov wrote:
> On 08/10/05, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> > The only thing I can comment on is updatable views. You can make
> > updatable views using RULEs. The only thing is that they're not
> > *automatically* updateable.
>
> OK, I'll make this correction. But for me, updatable views are views
> for which DBMS supports insert/update/delete operations as for
> tables. Ideally, people shouldn't distinguish table and view - that's
> what theory stands for (see Date's thoutghs about it:
> http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
> on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
> PostgreSQL doesn't support updates even for simple views such as
> select-with-restriction. What it does support - not updatable views,
> but some kind of INSTEAD OFF triggers (another form of).

You've got me confused. What are INSTEAD OF triggers?

PostgreSQL does support views that look exactly like tables. You can
decide on INSERT what to do whith fields not in the view, which columns
you allow UPDATE and what the semantics should be for DELETE if the
view is a join on multiple tables. All PostgreSQL doesn't do is create
these rules for you.

For an example see here, all the way at the bottom. RULEs are not
TRIGGERs:

http://www.varlena.com/varlena/GeneralBits/82.php

<snip>
> PK (in other words, possible duplicate rows). Nevertheless, all major
> commercial RDMSs support some subset of views that can be updated..
> SQL:2003 defines a quite large subset, but the definition is pretty
> mazy...

PostgreSQL allows any view to be updatable, no matter how complex it
is. You just have to create the rules yourself.

There have been attempts to automate the process, they just havn't been
clean enough to pass muster. And people who really want updateable
views can make them already.

Hope this clarifies things for you,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.