From: Robins Tharakan <tharakan@gmail.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: postgresql function not accepting null values in select statement
Date: Fri, 22 Feb 2008 15:04:06 +0530
Message-ID: <36af4bed0802220134n12831a64l6fce1364f53f8598@mail.gmail.com> (raw)
In-Reply-To: <36af4bed0802220132k613126b3la36f26d10fdaaad3@mail.gmail.com>
References: <001501c8752d$391c8e50$ab55aaf0$@com>
<47BE9058.9030909@archonet.com>
<001a01c87534$84eea280$8ecbe780$@com>
<36af4bed0802220132k613126b3la36f26d10fdaaad3@mail.gmail.com>
>
> 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
>
>
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: tharakan@gmail.com
Subject: Re: postgresql function not accepting null values in select statement
In-Reply-To: <36af4bed0802220134n12831a64l6fce1364f53f8598@mail.gmail.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