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

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 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>
> 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/i...omparison.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
>
>

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

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

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 [mailtogsql-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://www.postgresql.org/docs/curre...omparison.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
>

>
> 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
> >
>
>