On Mon, Oct 21, 2013 at 2:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Consider
SELECT volatile_function(i) FROM generate_series(1, 10) i LIMIT 1;
How many times should the volatile function get executed? If your answer is not "10", how is this different from the CTE case? This LIMIT clause is restricting the number of times the function executes in pretty much the same way that our definition of CTE evaluation does, AFAICS.
I don't think your example above is analogous, because in your example, you are asking how many times to execute the function, whereas in my example, the question is whether to execute the query at all.
If the outer statement of the CTE doesn't need to use the contents of the CTE, and there is no volatile function in there, then I agree that it's fine not to execute it.
But if there is a volatile function, then the CTE query should always be evaluated just like CREATE TEMP TABLE.
There is no question as to how many times to evaluate it here. It is just once.