Thread: postgresql function not accepting null values in select statement
<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>
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
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
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
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 statementTo: 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,
JyotiTIP 6: explain analyze is your friend
-----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)---------------------------
Can you try this...
CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
RETURNS SETOF t_functionaries AS
$BODY$
DECLARE
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
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
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
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
<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/>
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)
RobinsOn 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---------------------------(end of broadcast)---------------------------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
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
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
with
WHERE not(f.statecd IS DISTINCT FROM p_statecd))
will do the same.
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