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

On Mon, Oct 21, 2013 at 2:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle