Re: PL/PgSQL and NULL

On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> Andrew Perrin writes:
>
> > I'm trying to write what should be a simple function that returns the
> > minimim of two integers. The complication is that when one of the two
> > integers is NULL, it should return the other; and when both are NULL, it
> > should return NULL.
>
> Functions involving NULLs don't work well before version 7.1.
>

True but a little terse, aren't we Peter? Functions all return null if
any of their parameters are null, prior to v 7.1, as Peter pointed out.
In 7.1, they only behave this way if marked 'strict'.

Arguably, that's the _right_ behavior for the case your describing:
in tri-valued logic, NULL means UNKNOWN: it could be any value. So
min(x,NULL) is UNKNOWN for any value of x, since the NULL could be larger
or smaller. If you want to do it anyway, you'll have to code your logic
directly in the SQL query. You'll find the COALESCE function useful:
it returns the first non-NULL argument. Combined with CASE, you should
be able to do return the minimum, non-null entry.

Exact code left as an excercise for the reader. ;-)

Ross