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

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

On Mon, Oct 21, 2013 at 2:53 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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.

2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

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