"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> In the specific case of COALESCE, we could theoretically do that,
>> since the only computation it needs is "IS NULL" which is
>> datatype-independent.
> Well, in the SQL specification, COALESCE is defined as an abbreviation
> of the CASE predicate, so to the extent that anyone pays attention to
> the spec, this:
> COALESCE(a, b)
> should be treated identically to:
> CASE WHEN a IS NULL THEN a ELSE b END
... as indeed we do. That CASE will be handled the same way as the
COALESCE is, ie, resolve as text output for lack of a better idea.
>> In most situations, however, you can't evaluate the function without
>> knowledge of the datatype semantics. As an example, consider
>> NULLIF('0', '00'). This gives different answers if you suppose the
>> literals are text than if you suppose they are integers.
> That is the other CASE abbreviation. (The only other one.) So,
> according to how I read the spec, it should be identical to
> CASE WHEN '0' = '00' THEN NULL ELSE '0' END
Yes, and you're begging the question: what are the semantics
of that = operator? Without imputing a datatype to the literals,
you can't resolve it.
> It is probably a poor choice on the part of the standards committee to
> implement these abbreviations for the CASE predicate in a way the
> causes them to look so much like functions.
Whether it's a function has nothing to do with this. It's a question of
datatype-dependent semantics, and it would be the same no matter what
the visual appearance of the constructs was.
regards, tom lane