Re: coalesce for null AND empty strings - Mailing list pgsql-general

Don't forget to cc: the list!

Ferdinand Gassauer wrote:
> Am Freitag, 30. März 2007 schrieben Sie:
>> 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;
>
> OK this shortens the function, but does not help to "solve"  the coalesce
> problem
> coalecse(empty2null(var1),empty2null(var2),....empty2null(var-n))
> instead of
> coalecse2(var1,var2,...var-n)
>
> where the empty2null is doing it's job "inside" the coalesce.

Well, you can always write the four or five variations you want:
CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar) ...
CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar) ...
CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar,varchar) ...
etc.

> BTW I use now
> if rtrim(text_i,' ') = ...
> to remove all blanks
>
> Badly enough null, empty strings and strings with blanks are not easy to
> distinguish and in most apps it is even impossible for the user, so this case
> has to be addressed somewhere.

Well, yes.

> a) make the application to handle this

Exactly. If you're going to allow NULLs to the user interface you'll
need some way to display them. If it's an unformatted text-field (e.g.
"description" or "name" you probably want NOT NULL.

> b) write a trigger on every table char not null field

Yes - if you want to trim leading/trailing spaces automatically. The
other thing you can do is define checks to make sure the first/last
character are not a space in the database, and the automatic trimming in
the application.

> c) have a confortable function, where needed. that's the idea

I'm still not sure where these nulls are coming from, if your
application isn't generating them.

--
   Richard Huxton
   Archonet Ltd