pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Andrew Perrin <aperrin@socrates.berkeley.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/PgSQL and NULL
Date: Sun, 11 Mar 2001 16:19:20 -0600
Message-ID: <20010311161920.A16066@rice.edu> (raw)
In-Reply-To: <Pine.LNX.4.30.0103112237250.809-100000@peter.localdomain>
References: <Pine.LNX.4.21.0103111536210.6221-100000@nujoma.perrins>
	<Pine.LNX.4.30.0103112237250.809-100000@peter.localdomain>


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



view thread (6+ messages)  latest in thread

Message-ID: <20010311161920.A16066@rice.edu>
Permalink:  ../20010311161920.A16066@rice.edu/
Also on:    postgresql.org/message-id/20010311161920.A16066@rice.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: reedstrm@rice.edu, aperrin@socrates.berkeley.edu
  Subject: Re: PL/PgSQL and NULL
  In-Reply-To: <20010311161920.A16066@rice.edu>
  References: <Pine.LNX.4.21.0103111536210.6221-100000@nujoma.perrins> <Pine.LNX.4.30.0103112237250.809-100000@peter.localdomain> <20010311161920.A16066@rice.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox