NULL values and string

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