Re: coalesce for null AND empty strings

[Richard Huxton <dev(at)archonet(dot)com>]

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