Thread: are views obsolete ?

Dear all

given the power of SETOF/ROWTYPE functions etc are select-only
views becoming obsolete/redundant ? IOW, do views only make
sense any longer when I add delete/insert/update rules to them
because the "select aggregation" advantage can be achieved
with SRFs, too (and more powerful at that) ? And even then
INSERTs could be replaced by standard functions, too (as could
updates/deletes but that would be ugly because one would have
to group where-condition values with update-attribute values,
at least for the UPDATE case).

I have not found a thread discussing the *relative advantages*
of views vs. SRFs in the archives...  I am sure it's quite
clear to the more involved people. The only advantage of SRFs
over views that I can see is that in an SRF I have the power
of procedural languages immediately available while in a view
definition I would have to call individual functions to
dynamically computer column values.

Pointers would be appreciated.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> given the power of SETOF/ROWTYPE functions etc are select-only
> views becoming obsolete/redundant ?

Not hardly.  The planner can optimize queries involving views, since it
can see what's inside the view definition.  SRFs are black boxes and
thus not optimizable.  For example, in
    SELECT * FROM my_srf() WHERE foo
there's no way to push the "foo" condition down inside my_srf().

            regards, tom lane

Tom Lane wrote:

>Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>
>
>>given the power of SETOF/ROWTYPE functions etc are select-only
>>views becoming obsolete/redundant ?
>>
>>
>
>Not hardly.  The planner can optimize queries involving views, since it
>can see what's inside the view definition.  SRFs are black boxes and
>thus not optimizable.  For example, in
>    SELECT * FROM my_srf() WHERE foo
>there's no way to push the "foo" condition down inside my_srf().
>
>
>
Does that include SQL functions ?  i.e.


CREATE FUNCTION ParameterizedQuery(integer) RETURNS setof c AS '
   select $1 as a, b from c where $1 = c.z + c.x;
' LANGUAGE SQL;

Gianni Mariani <gianni@mariani.ws> writes:
> Tom Lane wrote:
>> Not hardly.  The planner can optimize queries involving views, since it
>> can see what's inside the view definition.  SRFs are black boxes and
>> thus not optimizable.

> Does that include SQL functions ?

Hm.  A SQL function that contains a single SELECT could probably be
inlined, but it's not at the moment.  CVS tip does inline SQL functions
that return scalars, but not sets.  Good idea ...

            regards, tom lane