pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql function not accepting null values in select statement
11+ messages / 5 participants
[nested] [flat]
* postgresql function not accepting null values in select statement
@ 2008-02-22 08:30 Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jyoti Seth @ 2008-02-22 08:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
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.
I have also changed the value of transform_null_equals = on in the conf file
and restarted postgresql. But it is still giving error.
Is there any possible solution.
Thanks,
Jyoti
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values in select statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-22 09:05 ` Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2008-02-22 09:05 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values in select statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
@ 2008-02-22 09:22 ` Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:33 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:34 ` Re: postgresql function not accepting null values in select statement Robins Tharakan <tharakan@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 3 replies; 11+ messages in thread
From: Jyoti Seth @ 2008-02-22 09:22 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values in select statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-22 09:33 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2008-02-22 09:33 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values in select statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-22 09:34 ` Robins Tharakan <tharakan@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Robins Tharakan @ 2008-02-22 09:34 UTC (permalink / raw)
To: pgsql-sql
>
> 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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values inselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-22 09:35 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
2 siblings, 1 reply; 11+ messages in thread
From: Bart Degryse @ 2008-02-22 09:35 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values inselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-22 16:30 ` johnf <jfabiani@yolo.com>
2008-02-25 05:20 ` Re: postgresql function not accepting null values inselect statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-25 07:03 ` Re: postgresql function not accepting null values inselect statement Robins Tharakan <tharakan@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: johnf @ 2008-02-22 16:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values inselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
@ 2008-02-25 05:20 ` Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-25 06:56 ` Re: postgresql function not accepting null values inselect statement Robins Tharakan <tharakan@gmail.com>
2008-02-25 08:05 ` Re: postgresql function not accepting null valuesinselect statement Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 2 replies; 11+ messages in thread
From: Jyoti Seth @ 2008-02-25 05:20 UTC (permalink / raw)
To: 'johnf' <jfabiani@yolo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values inselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
2008-02-25 05:20 ` Re: postgresql function not accepting null values inselect statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-25 06:56 ` Robins Tharakan <tharakan@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Robins Tharakan @ 2008-02-25 06:56 UTC (permalink / raw)
To: pgsql-sql
>
> 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
> >
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null valuesinselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
2008-02-25 05:20 ` Re: postgresql function not accepting null values inselect statement Jyoti Seth <jyotiseth2001@gmail.com>
@ 2008-02-25 08:05 ` Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 11+ messages in thread
From: Bart Degryse @ 2008-02-25 08:05 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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".
http://www.postgresql.org/docs/8.2/static/functions-comparisons.html
http://www.postgresql.org/docs/8.1/static/functions-comparisons.html
http://www.postgresql.org/docs/8.0/static/functions-comparisons.html
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 ( http://archives.postgresql.org/ )
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: postgresql function not accepting null values inselect statement
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Re: postgresql function not accepting null values in select statement Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Re: postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
@ 2008-02-25 07:03 ` Robins Tharakan <tharakan@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Robins Tharakan @ 2008-02-25 07:03 UTC (permalink / raw)
To: johnf <jfabiani@yolo.com>; +Cc: pgsql-sql
http://www.postgresql.org/docs/current/static/functions-comparison.html
This document states this:
Lets assume:
A = NULL
B = 10
C = NULL
SELECT 1 WHERE A = B returns no rows
SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL)
SELECT 1 WHERE A IS NOT DISTINCT FROM C returns 1 row.
essentially the third SQL statement works because it is equivalent to this:
SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C)
*Robins*
On Fri, Feb 22, 2008 at 10:00 PM, johnf <jfabiani@yolo.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-02-25 08:05 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-22 08:30 postgresql function not accepting null values in select statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:05 ` Richard Huxton <dev@archonet.com>
2008-02-22 09:22 ` Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-22 09:33 ` Richard Huxton <dev@archonet.com>
2008-02-22 09:34 ` Robins Tharakan <tharakan@gmail.com>
2008-02-22 09:35 ` Re: postgresql function not accepting null values inselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-22 16:30 ` Re: postgresql function not accepting null values inselect statement johnf <jfabiani@yolo.com>
2008-02-25 05:20 ` Re: postgresql function not accepting null values inselect statement Jyoti Seth <jyotiseth2001@gmail.com>
2008-02-25 06:56 ` Re: postgresql function not accepting null values inselect statement Robins Tharakan <tharakan@gmail.com>
2008-02-25 08:05 ` Re: postgresql function not accepting null valuesinselect statement Bart Degryse <Bart.Degryse@indicator.be>
2008-02-25 07:03 ` Re: postgresql function not accepting null values inselect statement Robins Tharakan <tharakan@gmail.com>
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