Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
> 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
> So yeah, we could make COALESCE into a special-case wart in the type
> system and have it able to execute without inferring a type for the
> arguments. I don't think that would be a net improvement in the
> system's astonishment quotient, however; people would just be
> confused why COALESCE behaves differently from everything else.
Not if they notice that COALESCE and NULLIF are documented (quite
properly) on the "conditional expressions" page, along with the CASE
predicate:
http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html
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.
-Kevin