Re: COALESCE implementation question

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> Well, the standard is perfectly clear, isn't it? If V(1) has side
>> effects then trying to optimize this into just one evaluation of V(1)
>> will generate non-spec-compliant results.

> At least with the new function manager, if I feel te need I can write a
> 'CoalesceValues' function (at least for fixed numbers of parameters).

Mmm ... not really. You could detect nulls all right, but a function-
based version of COALESCE would evaluate *all* its arguments exactly
once, which is certainly wrong. If you don't stop evaluating with
the one you decide to return, you are neither compliant with the spec
nor safe (later expressions might yield errors if evaluated!)

> Sadly, my usual yard stick (Dec/RDB) seems to evaluate twice (at least
> that's what it's planner says). And dumping a view with a coalesce
> statement produces a CASE statement, so it probably has no choice.

Sounds like they do it the same as we do, ie, expand COALESCE into the
specified CASE equivalent on sight.

regards, tom lane