Re: postgresql function not accepting null values in select statement

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