Thread: NULL values and string
Hi, List!
I'm using PG8.1.
Are there any abilities to represent NULL values as string?
I'm doing something like this:
create function func(int4) returns varchar as'
declare
num_value alias for $1;
string_value varchar;
begin
string_value := \'input value = \' || num_value;
return string_value;
end
'language 'plpgsql';
If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'.
Of course, I can check input value like this:
if(num_value isnull) then
string_value := \'input value = NULL\';
else
string_value := \'input_value = \' || num_value;
end if;
But it is not laconic...
Sergey Karin
I'm using PG8.1.
Are there any abilities to represent NULL values as string?
I'm doing something like this:
create function func(int4) returns varchar as'
declare
num_value alias for $1;
string_value varchar;
begin
string_value := \'input value = \' || num_value;
return string_value;
end
'language 'plpgsql';
If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'.
Of course, I can check input value like this:
if(num_value isnull) then
string_value := \'input value = NULL\';
else
string_value := \'input_value = \' || num_value;
end if;
But it is not laconic...
Sergey Karin
Sergey Karin schrieb: > Hi, List! > > I'm using PG8.1. > > Are there any abilities to represent NULL values as string? > > I'm doing something like this: > > create function func(int4) returns varchar as' > declare > num_value alias for $1; > string_value varchar; > begin > > string_value := \'input value = \' || num_value; > return string_value; > > end > 'language 'plpgsql'; > > > If I einvoke my function with NULL argument, it return NULL. But I want > 'input value = NULL'. > Of course, I can check input value like this: > > if(num_value isnull) then > string_value := \'input value = NULL\'; > else > string_value := \'input_value = \' || num_value; > end if; > You can use COALESCE() create function func(int4) returns text as $$ declare num_value alias for $1; begin return 'input value = ' || COALESCE(num_value,'NULL'); end $$ language 'plpgsql'; (Id rather use more descriptive name for your function) Regards Tino
Sergey Karin wrote: > Hi, List! > > I'm using PG8.1. > > Are there any abilities to represent NULL values as string? Null isn't a real value. Try not to think of it as a value. http://archives.postgresql.org/pgsql-sql/2003-01/msg00222.php > num_value alias for $1; > string_value := \'input value = \' || num_value; > If I einvoke my function with NULL argument, it return NULL. But I want > 'input value = NULL'. Because NULL means unknown. A string with an unknown string appended to it is itself unknown. > Of course, I can check input value like this: > > if(num_value isnull) then > string_value := \'input value = NULL\'; > else > string_value := \'input_value = \' || num_value; > end if; > > But it is not laconic... Try something like: string_value := ''input_value = '' || COALESCE(num_value, 'a null'); -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Sergey Karin wrote: > >> Are there any abilities to represent NULL values as string? > > > Null isn't a real value. Try not to think of it as a value. > That being said, and with due credit elsewhere (http://www.varlena.com/varlena/GeneralBits/84.php), what I do is CREATE OR REPLACE FUNCTION public.textcat_null(text, text) RETURNS text AS 'SELECT textcat(COALESCE($1, ''''), COALESCE($2, ''''));' LANGUAGE sql' VOLATILE; CREATE OPERATOR public.||+( PROCEDURE = "public.textcat_null", LEFTARG = text, RIGHTARG = text); This goes against proper form, considering what NULL is designed for, but it sure is convenient. Regards, Berend Tober