COALESCE and NULLIF semantics
От | Kevin Grittner |
---|---|
Тема | COALESCE and NULLIF semantics |
Дата | |
Msg-id | 4AA6598A020000250002AB80@gw.wicourts.gov обсуждение исходный текст |
Ответы |
Re: COALESCE and NULLIF semantics
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: