Re: [SQL] null values to be replaced by a default value

Nuchanard Chiannilkulchai <nuch(at)valigene(dot)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