Re: Bug? Function with side effects not evaluated in CTE

Merlin Moncure-2 wrote
> If you wanted to structure the query so that the function was run only
> 10 times, that could be done trivially by moving the limit inside the
> CTE.

It is not trivial if you want to wrap the CTE expression into a VIEW and the
caller of the view only wishes to see/evaluate a subset of the data - even
if just to check the structure with some sample contents? A plain LIMIT is
limited though and usually an ORDER BY would be added for some kind of
top/bottom-N listing. In such a case the presence of the ORDER BY would
trigger the full scan and operate before the LIMIT.

And why is volatile so special here? A stable function seems just as good a
candidate for this behavior and even an immutable one. You are taking the
presence of "volatile" to mean that the function may be wrapping an
INSERT/UPDATE/DELETE and so should be executed in case that is true. The
examples are focused on avoiding expensive and needless SELECT evaluations.
Since INSERT/UPDATE/DELETE cannot occur in a non-volatile function, and
expensive table-processing/generating functions can be implemented as
stable, this would seem to work at the cost of further leveraging the
volatility modifier on the function - which defaults to VOLATILE.

Not sure if this is a good or bad decision to make but this thread hasn't
really provided a compelling use-case for making a change: the example
provided is too contrived. And while the current situation is somewhat
difficult to learn attaching yet more logic to the function volatility is
going to be difficult to learn too.

Wrapping INSERT/UPDATE/DELETE inside a non-procedural function is not that
common and in those cases where it is used it is most often a standalone API
and not something that is going to be reasonably used in a CTE. Putting it
into a CTE is an attempt to avoid procedural logic and/or SQL scripting; but
those tools are there to solve just this kind of problem. The separation of
data insertion from data querying is just as desirable as separating data
from presentation.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-Function-with-side-effects-not-evaluated-in-CTE-tp5774792p5775098.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.