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

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