Обсуждение: 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