Skip site navigation (1) Skip section navigation (2)

Re: Unable to use VIEWS (Ok button remains shaded)

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
Cc: joseflores <jose(dot)floresv(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Unable to use VIEWS (Ok button remains shaded)
Date: 2010-09-17 02:00:32
Message-ID: 4C92CBC0.6080901@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Richard Broersma wrote:
> On Wed, Sep 15, 2010 at 6:29 PM, joseflores <jose(dot)floresv(at)gmail(dot)com> wrote:
>
>   
>> Once I try to do something (whatever that may be) with VIEWS, the OK button
>> never goes from gray to black (as if I didn't have all the privileges) to be
>> clicked on as to accept the creation or changes.
>>     
>
> Views are not update-able by default unlike a table.    It is possible
> to make a view update-able I you build custom rules into the view.
> However, I should mention that even with custom rules, you really
> shouldn't attempt to make an update-able view based upon a query that
> joins or unions two or more tables together.
>
>
> Here is how to make a view update-able:
> http://www.postgresql.org/docs/8.4/interactive/rules-views.html#RULES-VIEWS-UPDATE
>
>
> And here is the current stance taken on the use of update-able views:
> http://wiki.postgresql.org/wiki/Updatable_views
>
>   
I always wandered why would anybody want to update a view? View is, by 
definition, a stored query which is executed frequently enough to be 
given its own first name, middle name and a family name.  If the view is 
being updated, the middle name is  F, just as in the case of Bucky Dent. 
I don't see  any database design pattern which would necessitate  
updating a view. That is a perversion, not unlike putting ketchup on a 
hot dog.
Statements with views are notoriously hard to optimize. If views are 
involved, the access path may be skewed, there is a join involved and 
optimizer doesn't always select the right access path, resulting in a 
very bad performance. Things are exacerbated in Postgres which doesn't 
allow programmers to influence optimizer, which is in my opinion a big 
philosophical mistake.
Oracle doesn't have "UPDATE FROM" as does Postgres, which sometimes 
makes updating a view necessary, but Postgres has a beautiful "update 
from" clause which makes such escapades completely unnecessary.
    scott=# update emp e1 set sal=e2.sal 
    from emp1 e2
    scott-# where e1.empno=e2.empno;
    UPDATE 14
    Time: 1.138 ms
PS:
----
EnterpriseDB is a company located in Boston, MA, the reference to Bucky 
Dent shouldn't be considered too cryptic. For the people unfamiliar with 
the legend of the Red Sox and the "curse of the Bambino", here are the 
Wikipedia entries for Bucky Dent and the curse:
http://en.wikipedia.org/wiki/Bucky_Dent
http://en.wikipedia.org/wiki/Curse_of_the_Bambino

-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 


In response to

Responses

pgsql-novice by date

Next:From: Richard BroersmaDate: 2010-09-17 03:53:44
Subject: Re: Unable to use VIEWS (Ok button remains shaded)
Previous:From: LewDate: 2010-09-17 00:22:21
Subject: Re: Adding seconds to a time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group