[SQL] PL/PgSQL and NULL
Greetings- 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. Here's what I've written: CREATE FUNCTION min(int4, int4) RETURNS int4 AS 'BEGIN IF $1 ISNULL THEN RETURN $2; ELSE IF $2 ISNULL THEN RETURN $1; ELSE IF $1 > $2 THEN RETURN $2; ELSE RETURN $1; END IF; END IF; END IF; END;' LANGUAGE 'plpgsql'; and here's what I get: fgdata=# select min(10, NULL); min - (1 row) so it looks like, for whatever reason, it's returning NULL when it should be returning 10. Can anyone offer advice? Thanks. -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin [EMAIL PROTECTED] - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] PL/PgSQL and NULL
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. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 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 [EMAIL PROTECTED] - [EMAIL PROTECTED] 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 > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/PgSQL and NULL
I think that is a bug in plpgsql, when passing a NULL into a plpgsql defined function, it treats other arguments as NULL also, you can use raise notice in your function to watch this buggy thing(see following). Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sun, 11 Mar 2001, Andrew Perrin wrote: > Greetings- > > 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. Here's what I've written: > > CREATE FUNCTION min(int4, int4) > RETURNS int4 > AS 'BEGIN raise notice ''arg1 is % arg2 is %'',$1,$2; -- debugging > IF $1 ISNULL > THEN > RETURN $2; > ELSE >IF $2 ISNULL >THEN > RETURN $1; >ELSE > IF $1 > $2 > THEN > RETURN $2; > ELSE > RETURN $1; > END IF; >END IF; > END IF; > END;' > LANGUAGE 'plpgsql'; > > and here's what I get: > > fgdata=# select min(10, NULL); > min > - > > (1 row) > > so it looks like, for whatever reason, it's returning NULL when it should > be returning 10. Can anyone offer advice? > > Thanks. > > -- > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology > Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin > [EMAIL PROTECTED] - [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
