Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL

Michael J Davis <michael.j.davis@tvguide.com> writes:

Create an nz(int4) or nvl(int4) function that returns 0 if $1 is null.
You could also create nz(text) that returns '' when $1 is null.

Also, you can use the COALESCE() standard function to handle this sort
of thing: COALESCE(a,b,...) basically returns the first non-null value
in its argument list. So COALESCE(x,0) or COALESCE(x,'') would handle
the above requirements.

COALESCE is a shorthand form of a CASE expression, which allows for
even more general if-then-else calculations.

CASE is present but a tad buggy in 6.4 (IIRC, it fails if used in a
SELECT that joins more than one table). There are no known problems
with it in 6.5.

regards, tom lane