Re: postgresql function not accepting null values in select statement

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