Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
[Correcting typo below.]
>> 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 [NOT] 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.
I'm surprised to find that CASE behaves this way, too. At least
there's an internal consistency to this, even if I think it's wrong on
all counts.
test=# select pg_typeof(case when null is not null then null else null
end);pg_typeof
-----------text
(1 row)
I think the better idea is to say that the type is still unknown.
>> 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.
Yeah -- my argument would be that the = operator in NULLIF should be
treated the same as if the function-like abbreviation were rewritten
to the full CASE predicate. It doesn't surprise me that that is taken
as text, given that they are both unadorned character string literals.
The surprise here (for me at least) that the following generates a
null of type text instead of matching the non-NULL input argument or
(failing that) unknown, assuming the rewrite of NULLIF(a, b) to the
equivalent CASE predicate:
test=# select pg_typeof(case when null = 0 then null else null end);pg_typeof
-----------text
(1 row)
Frankly, I'm dubious about treating a character string literal as
being of unknown type in the first place, but I can see where it is
a useful convenience. Where the wheels really come off for me is in
automagically going from unknown type to text on any form of CASE
predicate.
-Kevin
