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
