Thread: null values to be replaced by a default value

Hi,
   To return one value not null, i've created a function :

CREATE FUNCTION isnull (int4,int4 ) RETURNS int4 AS '       BEGIN               IF ($1 IS NULL) THEN
RETURN$2;                ELSE                RETURN $1;               END IF;       END;       ' LANGUAGE 'plpgsql';
 

select isnull(my_int,0) as number, sample_id from my_table;

but I got
number  |sample_id
------+---------             |       13             |       15

What should I do to have '0' in the column 'number' instead of NULL ?
Thanks,
   nuch

Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
>     To return one value not null, i've created a function :

You can't really do that with functions right now --- any null
input to a function causes the result to be null.  (There's
been discussion on the hackers list about improving this,
but it won't happen before 6.6 at the earliest.)

Fortunately, the standard COALESCE operator does what you want
already: COALESCE(a,b) produces the result you were looking for,
and you won't need to make a new copy for every data type either.

Basically, COALESCE takes any number of arguments and returns the
first one that's not NULL.  It's a shorthand for a CASE expression,
(CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ...)
        regards, tom lane