Re: PL/PgSQL and NULL

Thanks - I'll work on it that way. I know the general-case min() should
probably return NULL if any element is null, but I'm in need of what I
described for a specific case in which the result should be "the minimum
non-null entry", which of course is NULL if all entries are null.

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
aperrin(at)socrates(dot)berkeley(dot)edu - aperrin(at)igc(dot)apc(dot)org

On Sun, 11 Mar 2001, Ross J. Reedstrom wrote:

>
> 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
>