Re: postgresql function not accepting null values inselect statement

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(at)yolo(dot)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
>