>
> 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(at)gmail(dot)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(at)archonet(dot)com>
> Cc: pgsql-sql(at)postgresql(dot)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(at)archonet(dot)com]
> Sent: Friday, February 22, 2008 2:35 PM
> To: Jyoti Seth
> Cc: pgsql-sql(at)postgresql(dot)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
>
>