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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
