Given the desire to defer pinning the type of a literal, for better
support of user defined types, I think the current semantics of all
the conditional expressions:
http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html
are somewhat broken.
As a quick sample of something which I believe implements the correct
semantics for COALESCE and NULLIF, see the functions below. I'm not
suggesting that it would be best to treat these as functions, just
showing what I think the correct semantics would be. I also think
that the ease of definition shows that such an interpretation would
not necessarily be fairly characterized as a huge wart on the type
system.
create function "coalesce"(v1 unknown, v2 unknown)
returns unknown language plpgsql as
'begin if v1 is not null then return v1; end if; return v2; end;';
create function "coalesce"(v1 anyelement, v2 anyelement)
returns anyelement language plpgsql as
'begin if v1 is not null then return v1; end if; return v2; end;';
create function "nullif"(v1 unknown, v2 unknown)
returns unknown language plpgsql as
'begin if v1 = v2 then return null; end if; return v1; end;';
create function "nullif"(v1 anyelement, v2 anyelement)
returns anyelement language plpgsql as
'begin if v1 = v2 then return null; end if; return v1; end;';
select "coalesce"(null, null), pg_typeof(("coalesce"(null, null)));coalesce | pg_typeof
----------+----------- | unknown
(1 row)
select "coalesce"(null, '1'), pg_typeof(("coalesce"(null, '1')));coalesce | pg_typeof
----------+-----------1 | unknown
(1 row)
select "coalesce"('1', '2'), pg_typeof(("coalesce"('1', '2')));coalesce | pg_typeof
----------+-----------1 | unknown
(1 row)
select "coalesce"(null, '1'::text), pg_typeof(("coalesce"(null,
'1'::text)));coalesce | pg_typeof
----------+-----------1 | text
(1 row)
select "coalesce"(null, 1), pg_typeof(("coalesce"(null, 1)));coalesce | pg_typeof
----------+----------- 1 | integer
(1 row)
The CASE predicate would also need to behave in this manner. Probably
GREATEST and LEAST, as well.
-Kevin