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