Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9030E2E002E for ; Mon, 25 Feb 2008 03:03:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47934-02 for ; Mon, 25 Feb 2008 03:03:10 -0400 (AST) X-Greylist: delayed 00:06:55.882811 by SQLgrey-1.7.5 Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.239]) by postgresql.org (Postfix) with ESMTP id 3D9262E010A for ; Mon, 25 Feb 2008 03:03:09 -0400 (AST) Received: by hu-out-0506.google.com with SMTP id 22so1838836hug.23 for ; Sun, 24 Feb 2008 23:03:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Pxh0H+bWwRGU5+0NDs6TwugsNT7zTWhPona/6l0usiw=; b=HZiszxGrVDjEhZLkWXFMbiht6uye2dvTXecGJqqwCI5AN54BB8WHsIld1NMuosE6xt5JocVoKI/77l41+eutCFX+7GykeOQMR3NA8l4/Ccdb5XKDnsa/DskgpGXp4BQu6Y/8AeAjHgw9leWo6kvQErN5lGinJOC0/Tf+Y1zY5x8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=avNJ7dL3NPURjxfh8qCowEPxdKoJ27MaEly2lrU3t9TWdbVrHYuG2cFFTdve0X06gxY+LyUbDmkqap6paAlN3EVjDq+bPrNjweIcikunrW4MpihEqCQEotttdZUfYM/szkLKONI95ceFeV5SIoBDoa9k5NBvnoNaN35IV4HF1FQ= Received: by 10.82.112.3 with SMTP id k3mr5047830buc.33.1203922988448; Sun, 24 Feb 2008 23:03:08 -0800 (PST) Received: by 10.82.112.18 with HTTP; Sun, 24 Feb 2008 23:03:08 -0800 (PST) Message-ID: <36af4bed0802242303w3bca871cgab3d004a1414faed@mail.gmail.com> Date: Mon, 25 Feb 2008 12:33:08 +0530 From: "Robins Tharakan" To: johnf Subject: Re: postgresql function not accepting null values inselect statement Cc: pgsql-sql@postgresql.org In-Reply-To: <200802220830.56917.jfabiani@yolo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13232_25872311.1203922988444" References: <001501c8752d$391c8e50$ab55aaf0$@com> <001a01c87534$84eea280$8ecbe780$@com> <47BEA582.A3DD.0030.0@indicator.be> <200802220830.56917.jfabiani@yolo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/179 X-Sequence-Number: 30270 ------=_Part_13232_25872311.1203922988444 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline http://www.postgresql.org/docs/current/static/functions-comparison.html This document states this: Lets assume: A = NULL B = 10 C = NULL SELECT 1 WHERE A = B returns no rows SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL) SELECT 1 WHERE A IS NOT DISTINCT FROM C returns 1 row. essentially the third SQL statement works because it is equivalent to this: SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C) *Robins* On Fri, Feb 22, 2008 at 10:00 PM, johnf wrote: > On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > > Can you try this... > > > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > > RETURNS SETOF t_functionaries AS > > $BODY$ > > DECLARE > > rec t_functionaries%ROWTYPE; > > BEGIN > > FOR rec IN ( > > SELECT f.functionaryid, f.category, f.description > > FROM functionaries f > > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > > LOOP > > return next rec; > > END LOOP; > > return; > > END; > > $BODY$ > > LANGUAGE 'plpgsql' VOLATILE; > > > a newbie question. Could you explain why yours works? I don't understand > how > it works if p_statecd = NULL > > > -- > John Fabiani > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ------=_Part_13232_25872311.1203922988444 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline http://www.postgresql.org/docs/current/static/functions-comparison.html

This document states this:
Lets assume:
A = NULL
B = 10
C = NULL

SELECT 1 WHERE A = B returns no rows
SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL)
SELECT 1 WHERE A IS NOT DISTINCT FROM C returns 1 row.

essentially the third SQL statement works because it is equivalent to this:

SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C)

Robins


On Fri, Feb 22, 2008 at 10:00 PM, johnf <jfabiani@yolo.com> wrote:
On Friday 22 February 2008 01:35:47 am Bart Degryse wrote:
> Can you try this...
>
> CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
>   RETURNS SETOF t_functionaries AS
> $BODY$
> DECLARE
>   rec t_functionaries%ROWTYPE;
> BEGIN
>   FOR rec IN (
>     SELECT f.functionaryid, f.category, f.description
>     FROM functionaries f
>     WHERE f.statecd IS NOT DISTINCT FROM p_statecd)
>   LOOP
>     return next rec;
>   END LOOP;
>   return;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
a newbie question.  Could you explain why yours works?  I don't understand how
it works if p_statecd = NULL


--
John Fabiani

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_13232_25872311.1203922988444--