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

On Wed, Oct 16, 2013 at 7:14 PM, Rowan Collins <rowan(dot)collins(at)gmail(dot)com> wrote:
> On 17/10/2013 00:06, Merlin Moncure wrote:
>
> That being said, I do think it might be better behavior (and still
> technically correct per the documentation) if volatile query
> expressions were force-evaluated.
>
>
> This sounds reasonable for a "yes or no" case like this, but wouldn't it
> raise the question of how many times the function should be evaluated?
>
> What if the query looked more like this:
>
> with tt_created as
> (
> select fn_new_item(foo) as item
> from some_huge_table
> )
> select item
> from tt_created
> limit 10
>
>
> Should the CTE be calculated in its entirety, running the function for every
> row in some_huge_table? Or should it run at most 10 times?
>
> Which is desired would depend on the situation, but there's no real way to
> indicate in the syntax.

ISTM the answer is clearly "in its entirety". The premise is that the
optimization of non-evaluation of CTE queries is not dependent on
mechanics further down the chain if the CTE has volatile expressions.

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.

merlin