Received: from smtp012.mail.yahoo.com (smtp012.mail.yahoo.com [216.136.173.32]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2FG6Nx23914 for ; Thu, 15 Mar 2001 11:06:23 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from pd9575124.dip.t-dialin.net (HELO jupiter.jw.home) (217.87.81.36) by smtp.mail.vip.sc5.yahoo.com with SMTP; 15 Mar 2001 16:06:18 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id KAA03405; Thu, 15 Mar 2001 10:02:51 -0500 From: Jan Wieck Message-Id: <200103151502.KAA03405@jupiter.jw.home> Subject: Re: PL/PgSQL and NULL In-Reply-To: from Jie Liang at "Mar 11, 2001 05:58:40 pm" To: Jie Liang Date: Thu, 15 Mar 2001 10:02:51 -0500 (EST) CC: Andrew Perrin , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/310 X-Sequence-Number: 1974 Jie Liang wrote: > > 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). You're blaming the wrong code for it. It's an insufficience in the pre v7.1 function manager, not a bug in PL/pgSQL's handler. Jan > > Jie LIANG > > St. Bernard Software > > 10350 Science Center Drive > Suite 100, San Diego, CA 92121 > Office:(858)320-4873 > > jliang@ipinc.com > 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 > > aperrin@socrates.berkeley.edu - aperrin@igc.apc.org > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com