Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 21CBA2E00E6 for ; Fri, 22 Feb 2008 05:34:12 -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 83021-10 for ; Fri, 22 Feb 2008 05:34:08 -0400 (AST) Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.238]) by postgresql.org (Postfix) with ESMTP id 63D482E00D5 for ; Fri, 22 Feb 2008 05:34:08 -0400 (AST) Received: by hu-out-0506.google.com with SMTP id 22so393272hug.23 for ; Fri, 22 Feb 2008 01:34:06 -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:in-reply-to:mime-version:content-type:references; bh=JG4QnxpkRIF15Y/8UDCRabtU0xNP0Hy8HEnOAiK/Fvs=; b=pWi02frFpLCVNmJAroFvI+EPt7IY5nmLOMbXxr7dRamBiT2JJgNRqk0P7UhpMonjRBpesHv5XBSMb0tCt/Aptq9oO6iyTv5dH9wzuyYDiZlEfxPy1ypBiwCOyjw4/jZ2vKUWcRrapd/uFxqIMJXGIGD/N67faf1Uft6LoLY76Yc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=OCrMCp8jHd6mMZQgoCyOFsPLox9KuaRcKiAXYTlgRNacV88jLx4L+8o85fVvZo/ZxUDpP1sQhG+eFHD2/ZRnYziSlIchgbT/0IhHA9NNsxA2cbMD7M9CdEP83rxj/Xat7NUk3tLl5h4Gp7E1TKoV3ClTXPqudaoMAT+WpS25OWA= Received: by 10.82.166.9 with SMTP id o9mr19059706bue.34.1203672846854; Fri, 22 Feb 2008 01:34:06 -0800 (PST) Received: by 10.82.112.18 with HTTP; Fri, 22 Feb 2008 01:34:06 -0800 (PST) Message-ID: <36af4bed0802220134n12831a64l6fce1364f53f8598@mail.gmail.com> Date: Fri, 22 Feb 2008 15:04:06 +0530 From: "Robins Tharakan" To: pgsql-sql Subject: Re: postgresql function not accepting null values in select statement In-Reply-To: <36af4bed0802220132k613126b3la36f26d10fdaaad3@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_332_31741800.1203672846847" References: <001501c8752d$391c8e50$ab55aaf0$@com> <47BE9058.9030909@archonet.com> <001a01c87534$84eea280$8ecbe780$@com> <36af4bed0802220132k613126b3la36f26d10fdaaad3@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/162 X-Sequence-Number: 30253 ------=_Part_332_31741800.1203672846847 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > Hi, > > When you pass non-null values in p_statecd the result should work fine, > but when you pass NULL in p_statecd ... the equal operator stops to work as > you as expect it to. > > Please see this documentation: > http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html > from where I quote: "Do *not* write *expression* = NULL because NULL is > not "equal to" NULL. (The null value represents an unknown value, and it > is not known whether two unknown values are equal.) This behavior conforms > to the SQL standard." > > As the document suggests you may want to try this way out: > > .... WHERE f.statecd IS NOT DISTINCT FROM p_statecd > > This would take care of both NULL and non-NULL values. > > *Robins* > > > ---------- Forwarded message ---------- > From: Jyoti Seth > Date: Fri, Feb 22, 2008 at 2:52 PM > Subject: Re: [SQL] postgresql function not accepting null values in select > statement > To: Richard Huxton > Cc: pgsql-sql@postgresql.org > > > Hi, > > I have a the following procedure > > 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=p_statecd > > LOOP > return next rec; > END LOOP; > return; > end; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > In the functionaries table statecd is a null field. When I pass some > integer > value to the above procedure it works correctly but if I pass null value > in > p_statecd it doesn't show anything whereas it has values and if I write > the > select statement separately it gives values > > Thanks, > Jyoti > > -----Original Message----- > From: Richard Huxton [mailto:dev@archonet.com] > Sent: Friday, February 22, 2008 2:35 PM > To: Jyoti Seth > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] postgresql function not accepting null values in select > statement > > Jyoti Seth wrote: > > > > If I pass null value as the parameter of postgresql function, which is > used > > in the where clause of select statement is not functioning properly. > > Either: > > 1. You're talking about frooble(), in which case it's supposed to do that. > > or > > 2. You'll need to tell us what function it is, how you're using it and > what you think should happen. > > My guess is that you're getting a null as the result and that's not > doing what you'd expect in your where clause. > > > -- > Richard Huxton > Archonet Ltd > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > ------=_Part_332_31741800.1203672846847 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi,

When you pass non-null values in p_statecd the result should work fine, but when you pass NULL in p_statecd ... the equal operator stops to work as you as expect it to.

Please see this documentation: http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html
from where I quote: "Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard."

As the document suggests you may want to try this way out:

.... WHERE f.statecd IS NOT DISTINCT FROM p_statecd

This would take care of both NULL and non-NULL values.

Robins


---------- Forwarded message ----------
From: Jyoti Seth <jyotiseth2001@gmail.com>
Date: Fri, Feb 22, 2008 at 2:52 PM
Subject: Re: [SQL] postgresql function not accepting null values in select statement
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org


Hi,

I have a the following procedure

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

       LOOP
       return next rec;
       END LOOP;
      return;
  end;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

In the functionaries table statecd is a null field. When I pass some integer
value to the above procedure it works correctly but if I pass null value in
p_statecd it doesn't show anything whereas it has values and if I write the
select statement separately it gives values

Thanks,
Jyoti

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, February 22, 2008 2:35 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] postgresql function not accepting null values in select
statement

Jyoti Seth wrote:
>
> If I pass null value as the parameter of postgresql function, which is
used
> in the where clause of select statement is not functioning properly.

Either:

1. You're talking about frooble(), in which case it's supposed to do that.

or

2. You'll need to tell us what function it is, how you're using it and
what you think should happen.

My guess is that you're getting a null as the result and that's not
doing what you'd expect in your where clause.


--
  Richard Huxton
  Archonet Ltd


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


------=_Part_332_31741800.1203672846847--