Thread: postgresql function not accepting null values in select statement

postgresql function not accepting null values in select statement

From
"Jyoti Seth"
Date:
<div class="Section1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">Hi,</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green"> </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">If I pass null value as the parameter of postgresql function, which is used in the where clause of select
statementis not functioning properly.</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS
ReferenceSans Serif","sans-serif"; 
color:green"> </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">I have also changed the value of transform_null_equals = on in the conf file and restarted postgresql. But
itis still giving error. </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green"> </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">Is there any possible solution.</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS
ReferenceSans Serif","sans-serif"; 
color:green"> </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">Thanks,</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"MS Reference Sans
Serif","sans-serif";
color:green">Jyoti </span></div>

Re: postgresql function not accepting null values in select statement

From
Richard Huxton
Date:
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


Re: postgresql function not accepting null values in select statement

From
"Jyoti Seth"
Date:
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.descriptionFROM 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



Re: postgresql function not accepting null values in select statement

From
Richard Huxton
Date:
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


Re: postgresql function not accepting null values in select statement

From
"Robins Tharakan"
Date:
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


Re: postgresql function not accepting null values inselect statement

From
"Bart Degryse"
Date:
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;

>>> "Jyoti Seth" <jyotiseth2001@gmail.com> 2008-02-22 10:22 >>>
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

Re: postgresql function not accepting null values inselect statement

From
johnf
Date:
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


Re: postgresql function not accepting null values inselect statement

From
"Jyoti Seth"
Date:
I have tried this, but it is showing following error:
ERROR: syntax error at or near "DISTINCT"
SQL state: 42601

Thanks,
Jyoti


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of johnf
Sent: Friday, February 22, 2008 10:01 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] postgresql function not accepting null values inselect
statement

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



Re: postgresql function not accepting null values inselect statement

From
"Robins Tharakan"
Date:
<a
href="http://www.postgresql.org/docs/current/static/functions-comparison.html">http://www.postgresql.org/docs/current/static/functions-comparison.html</a><br
/><br/>This document states this:<br />Lets assume: <br />A = NULL<br /> B = 10<br />C = NULL<br /><br />SELECT 1 WHERE
A= B returns no rows<br />SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL)<br /> SELECT 1 WHERE
AIS NOT DISTINCT FROM C returns 1 row.<br /><br />essentially the third SQL statement works because it is equivalent to
this:<br/><br />SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C)<br /><br /><b>Robins</b><br /><br /><br /><div
class="gmail_quote">OnFri, Feb 22, 2008 at 10:00 PM, johnf <<a
href="mailto:jfabiani@yolo.com">jfabiani@yolo.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On
Friday22 February 2008 01:35:47 am Bart Degryse wrote:<br /> > Can you try this...<br /> ><br /> > CREATE OR
REPLACEFUNCTION getfunctionaries(p_statecd integer)<br /> >   RETURNS SETOF t_functionaries AS<br /> > $BODY$<br
/>> DECLARE<br /> >   rec t_functionaries%ROWTYPE;<br /> > BEGIN<br /> >   FOR rec IN (<br /> >    
SELECTf.functionaryid, f.category, f.description<br /> >     FROM functionaries f<br /> >     WHERE f.statecd IS
NOTDISTINCT FROM p_statecd)<br /> >   LOOP<br /> >     return next rec;<br /> >   END LOOP;<br /> >  
return;<br/> > END;<br /> > $BODY$<br /> > LANGUAGE 'plpgsql' VOLATILE;<br /> ><br /></div>a newbie
question. Could you explain why yours works?  I don't understand how<br /> it works if p_statecd = NULL<br /><font
color="#888888"><br/><br /> --<br /> John Fabiani<br /><br /> ---------------------------(end of
broadcast)---------------------------<br/> TIP 5: don't forget to increase your free space map settings<br
/></font></blockquote></div><br/> 

Re: postgresql function not accepting null values inselect statement

From
"Robins Tharakan"
Date:
What version of PostGreSQL are you using ?
Are you sure there was no typing error ? This SQL should work in the most recent version of PG.( at least version 8.1 onwards)

Robins


On Mon, Feb 25, 2008 at 10:50 AM, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
I have tried this, but it is showing following error:
ERROR: syntax error at or near "DISTINCT"
SQL state: 42601

Thanks,
Jyoti


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of johnf
Sent: Friday, February 22, 2008 10:01 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] postgresql function not accepting null values inselect
statement

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org


Re: postgresql function not accepting null valuesinselect statement

From
"Bart Degryse"
Date:
In the documentation "IS NOT DISTINCT FROM" only appears in version 8.2
The 8.0 and 8.1 documentation only mentions "IS DISTINCT FROM".
I haven't tried it, but probably replacing
     WHERE f.statecd IS NOT DISTINCT FROM p_statecd)
with
     WHERE not(f.statecd IS DISTINCT FROM p_statecd))
will do the same.
 

>>> "Jyoti Seth" <jyotiseth2001@gmail.com> 2008-02-25 6:20 >>>
I have tried this, but it is showing following error:
ERROR: syntax error at or near "DISTINCT"
SQL state: 42601

Thanks,
Jyoti


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of johnf
Sent: Friday, February 22, 2008 10:01 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] postgresql function not accepting null values inselect
statement

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org