Re: COALESCE and NULLIF semantics

Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Tue, 2009-09-08 at 13:18 -0500, Kevin Grittner wrote:
>> As a quick sample of something which I believe implements the
>> correct semantics for COALESCE and NULLIF, see the functions below.
>
> You might want to show before and after, so it's clear what you are
> suggesting to change.

OK. The only time it would be different from current behavior is when
all parameters are of unknown type -- the result would be unknown
rather than text:

select "coalesce"(null, null), pg_typeof("coalesce"(null, null));
coalesce | pg_typeof
----------+-----------
| unknown
(1 row)

select coalesce(null, null), pg_typeof(coalesce(null, null));
coalesce | pg_typeof
----------+-----------
| text
(1 row)

select "coalesce"(null, '1'), pg_typeof("coalesce"(null, '1'));
coalesce | pg_typeof
----------+-----------
1 | unknown
(1 row)

select coalesce(null, '1'), pg_typeof(coalesce(null, '1'));
coalesce | pg_typeof
----------+-----------
1 | text
(1 row)

select "coalesce"('1', '2'), pg_typeof("coalesce"('1', '2'));
coalesce | pg_typeof
----------+-----------
1 | unknown
(1 row)

select coalesce('1', '2'), pg_typeof(coalesce('1', '2'));
coalesce | pg_typeof
----------+-----------
1 | text
(1 row)

All other situations would follow current behavior. For example, this
is the same either way:

select "coalesce"(null, 1), pg_typeof("coalesce"(null, 1));
coalesce | pg_typeof
----------+-----------
1 | integer
(1 row)

I believe this would work better both for those coming from a straight
SQL standard perspective and for those who want to treat user defined
types as first class types.

-Kevin