From: Richard Huxton <dev@archonet.com>
To: Jyoti Seth <jyotiseth2001@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: postgresql function not accepting null values in select statement
Date: Fri, 22 Feb 2008 09:33:59 +0000
Message-ID: <47BE9707.3060109@archonet.com> (raw)
In-Reply-To: <001a01c87534$84eea280$8ecbe780$@com>
References: <001501c8752d$391c8e50$ab55aaf0$@com>
<47BE9058.9030909@archonet.com>
<001a01c87534$84eea280$8ecbe780$@com>
Jyoti Seth wrote:
> Hi,
>
> I have a the following procedure
>
> CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
> SELECT f.functionaryid, f.category,f.description
> FROM functionaries f
> where f.statecd=p_statecd
> 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
You can't be getting results from a query WHERE statecd = NULL, because
NULL = NULL returns NULL, which is equivalent to false in a WHERE clause.
If you want to check for NULL you need to use statecd IS NULL, but if
you are treating it like a value then you're using it incorrectly.
--
Richard Huxton
Archonet Ltd
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, jyotiseth2001@gmail.com
Subject: Re: postgresql function not accepting null values in select statement
In-Reply-To: <47BE9707.3060109@archonet.com>
* 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