Re: coalesce for null AND empty strings

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