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
