Re: Updateable views - Mailing list pgsql-hackers

 --- Greg Stark <gsstark@mit.edu> escribió: 
> 
> >  - What if we cannot create one of the three
> >    rules? 
> >    Make the rule not updateable at all? 
> >    Or create the rules we can? (i think this is
> >    the correct)
> 
> I seem to be in the minority here. But I think
> creating complex rules to fiddle with the updates 
> to translate them to the underlying tables is the
> wrong approach.
> 
> I think you want to extend the SQL syntax to allow
> updating views, and implement plan nodes and 
> executor functionality to handle them. 

What if someone want his views to be readonly? with
rules he can just drop rule. In the approach you
mention he cannot.

> So things like this works:
> 
> UPDATE (SELECT id,val FROM t) SET val=0 where id <
> 100
> 

???? You really do things like that??? For what?? I'm
asking because i do not know any situation when it
becomes usefull.

Views, conceptually, should have the same behavior a
table has, because you can use it to let some people
view part of your info without letting them touch the
table. Sometimes you need they can update the fields
they can see, but then how u can prevent them touching
other fields they have no rights to? Updateable views
are handy for that.

In your example is obvious that you can access to the
t table, why not do the update directly?? Besides,
this enforce to create privileges per columns rather
than per table.

> Then the rules you create on the views are just like
> the rules for SELECT, they simply mechanically 
> replace the view with the view definition.
> 
> I think this is the right approach because:
> 
> a) I think creating the general rules to transform
>    an update into an update on the underlying table 
>    will be extremely complex, and you'll only ever
be
>    able to handle the simplest cases. By handling
>    the view at planning time you'll be able to 
>    handle arbitrarily complex cases limited only by 
>    whether you can come up with reasonable
semantics.
> 

I don't think is *extremely complex* to create the
rules; but yes, there will be limitations.

> b) I think it's aesthetically weird to have
>    functionality that's only accessible via creating

>    DDL objects and then using them, and not 
>    accessible directly in a single SQL DML command. 
>    Ie, it would be strange to have to create 
>    a "temporary view" just in order to execute an 
>    update because there's no equivalent syntax 
>    available for use directly.
> 

????
alter table (SELECT id,val FROM t)     alter column val set default 3;
???? 


> > General Restrictions!!!
> > ---------------------------
> > - The column target list holds column fields only,
> >   that are retrieved from one base relation / view
> >   only. (NO joined views).
> 
> I know there are other uses for updatable views (eg
> implementing column-based security policies) but the

> _only_ reason I ever found them useful in Oracle
> was precisely for joined views. 

The NOTE i included in my last post says that oracle
do that with user_updateable_columns view and i
suggest the creation (or the extension of
pg_attribute) of a catalog to implement this. And i
state that can be useful to create joined updateable
views.

> They're the Oracle blessed method for achieving the 
> same performance win as Postgres's FROM clause.
> 
> So in Oracle you can do:
> 
> UPDATE (select a.val as newval, b.b_id, b.val from
> a,b where a.b_id = b.b_id) SET val = newval
> 

I think Postgres's UPDATE ... FROM is a lot more clear
to understand.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com