Re: postgresql function not accepting null values inselect statement

<a
href="http://www.postgresql.org/docs/current/static/functions-comparison.html">http://www.postgresql.org/docs/current/static/functions-comparison.html</a><br
/><br/>This document states this:<br />Lets assume: <br />A = NULL<br /> B = 10<br />C = NULL<br /><br />SELECT 1 WHERE
A= B returns no rows<br />SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL)<br /> SELECT 1 WHERE
AIS NOT DISTINCT FROM C returns 1 row.<br /><br />essentially the third SQL statement works because it is equivalent to
this:<br/><br />SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C)<br /><br /><b>Robins</b><br /><br /><br /><div
class="gmail_quote">OnFri, Feb 22, 2008 at 10:00 PM, johnf <<a
href="mailto:jfabiani@yolo.com">jfabiani@yolo.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On
Friday22 February 2008 01:35:47 am Bart Degryse wrote:<br /> > Can you try this...<br /> ><br /> > CREATE OR
REPLACEFUNCTION getfunctionaries(p_statecd integer)<br /> >   RETURNS SETOF t_functionaries AS<br /> > $BODY$<br
/>> DECLARE<br /> >   rec t_functionaries%ROWTYPE;<br /> > BEGIN<br /> >   FOR rec IN (<br /> >    
SELECTf.functionaryid, f.category, f.description<br /> >     FROM functionaries f<br /> >     WHERE f.statecd IS
NOTDISTINCT FROM p_statecd)<br /> >   LOOP<br /> >     return next rec;<br /> >   END LOOP;<br /> >  
return;<br/> > END;<br /> > $BODY$<br /> > LANGUAGE 'plpgsql' VOLATILE;<br /> ><br /></div>a newbie
question. Could you explain why yours works?  I don't understand how<br /> it works if p_statecd = NULL<br /><font
color="#888888"><br/><br /> --<br /> John Fabiani<br /><br /> ---------------------------(end of
broadcast)---------------------------<br/> TIP 5: don't forget to increase your free space map settings<br
/></font></blockquote></div><br/>