Re: COALESCE and NULLIF semantics

Peter Eisentraut <peter_e@gmx.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