Ferdinand Gassauer wrote:
Hi!
it would be great to have a coalesce2 function which treats empty strings as
null values.
Why? What is the use-case for this?
as far as I have seen, there are a lot of comments and coding solutions about
this, but none is an "easy" one and all make the code a bit more complicated
and more difficult to maintain.
I have created this function.
It's similar to nullif, but takes only ONE argument
create or replace function "empty2null"(text_i varchar)
returns varchar as $$
declare
text_p varchar;
begin
if text_i = ''
then text_p := null;
else text_p := text_i;
end if;
return text_p;
end;
$$ LANGUAGE plpgsql;
or even shorter:
CREATE OR REPLACE FUNCTION empty2null(varchar) RETURNS varchar AS $$
SELECT CASE WHEN $1 = '' THEN NULL ELSE $1 END;
$$ LANGUAGE SQL;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
