PL/pgSQL how to pass null values to the functions? - Mailing list pgsql-hackers

<p align="LEFT"><font color="#000000" face="Arial" size="2">I have plpgsql function</font><font color="#000000"
face="Arial"size="2"> that</font><font color="#000000" face="Arial" size="2"> updates the table and</font> <font
color="#000000"face="Arial" size="2">may have (and also may not have)</font><font color="#000000" face="Arial"
size="2">null</font><font color="#000000" face="Arial" size="2"></font> <font color="#000000" face="Arial"
size="2">parameters</font><fontcolor="#000000" face="Arial" size="2">:</font><p align="LEFT"><font color="#000000"
face="Arial"size="2">create function f_test(int4, int4) returns int4 as</font><p align="LEFT"><font color="#000000"
face="Arial"size="2">'</font><p align="LEFT"><font color="#000000" face="Arial" size="2">    declare</font><ul><ul><p
align="LEFT"><fontcolor="#000000" face="Arial" size="2">v1 alias for $1;</font></ul></ul><p align="LEFT">        <font
color="#000000"face="Arial" size="2">v2 alias for $2;</font><p align="LEFT">        <font color="#000000" face="Arial"
size="2">v3int4;</font><p align="LEFT"><font color="#000000" face="Arial" size="2">    begin</font><p
align="LEFT">       <font color="#000000" face="Arial" size="2">raise notice ''v1 = %'', v1;</font><p
align="LEFT">       <font color="#000000" face="Arial" size="2">update _</font><font color="#000000" face="Arial"
size="2">testtable</font><fontcolor="#000000" face="Arial" size="2"> set</font> <font color="#000000" face="Arial"
size="2">a</font><fontcolor="#000000" face="Arial" size="2"> = v1 where</font> <font color="#000000" face="Arial"
size="2">b</font><fontcolor="#000000" face="Arial" size="2"> = v2;</font><p align="LEFT">        <font color="#000000"
face="Arial"size="2">select into v3</font> <font color="#000000" face="Arial" size="2">a</font><font color="#000000"
face="Arial"size="2"> from _</font><font color="#000000" face="Arial" size="2">testtable</font><font color="#000000"
face="Arial"size="2"> where</font> <font color="#000000" face="Arial" size="2">b</font><font color="#000000"
face="Arial"size="2"> = v2;</font><p align="LEFT">        <font color="#000000" face="Arial" size="2">raise notice ''v3
=%'', v3;</font><p align="LEFT">        <font color="#000000" face="Arial" size="2">return v3;</font><p
align="LEFT"><fontcolor="#000000" face="Arial" size="2">    end;</font><p align="LEFT"><font color="#000000"
face="Arial"size="2">' language 'plpgsql';</font><p align="LEFT"><font color="#000000" face="Arial"
size="2">C</font><fontcolor="#000000" face="Arial" size="2">olumn a</font> <font color="#000000" face="Arial"
size="2">in_</font><i><font color="#000000" face="Arial" size="2">testable</font></i><i> <font color="#000000"
face="Arial"size="2">can have null value</font></i><i><font color="#000000" face="Arial" size="2">. When I
do</font></i><i><font color="#000000" face="Arial" size="2">‘</font></i><i><font color="#000000" face="Arial"
size="2">sele</font></i><i><fontcolor="#000000" face="Arial" size="2">ct</font></i><i> <font color="#000000"
face="Arial"size="2">f</font></i><font color="#000000" face="Arial" size="2">_test(</font><font color="#000000"
face="Arial"size="2">1,</font><font color="#000000" face="Arial" size="2">1</font><font color="#000000" face="Arial"
size="2">)</font><fontcolor="#000000" face="Arial" size="2">;’</font><font color="#000000" face="Arial" size="2"> then
everythingis working fine. The row is updated</font><font color="#000000" face="Arial" size="2"> (v1</font><font
color="#000000"face="Arial" size="2"> = v3 = 1</font><font color="#000000" face="Arial" size="2">)</font><font
color="#000000"face="Arial" size="2">.</font><p align="LEFT"><font color="#000000" face="Arial" size="2">But when I do
‘selectf_test(</font><font color="#000000" face="Arial" size="2">null,1);’</font><font color="#000000" face="Arial"
size="2">nothing happens</font> <font face="Wingdings" size="2">L</font><font color="#000000" face="Arial" size="2">.
Noerrors reported, but ‘select a from _</font><font color="#000000" face="Arial" size="2">testable</font><font
color="#000000"face="Arial" size="2"> where</font><font color="#000000" face="Arial" size="2"></font> <font
color="#000000"face="Arial" size="2">b=1;’ returns 1</font><font color="#000000" face="Arial" size="2"> (v1 = v3 =
<null>)</font><fontcolor="#000000" face="Arial" size="2">.</font><p align="LEFT"><font color="#000000"
face="Arial"size="2">Where am I wrong?</font><p><b><font face="Arial Cyr" size="2">CONFIDENTIALITY NOTICE</font></b><br
/><fontface="Arial Cyr" size="2">This email and any files transmitted with it are confidential and are intended solely
forthe use of the individual or entity to whom they are addressed. This communication represents the originator's
personalviews and opinions, which do not necessarily reflect those of FastNet Solutions Company. If you are not the
originalrecipient or the person responsible for delivering the email to the intended recipient, be advised that you
havereceived this email in error, and that any use, dissemination, forwarding, printing, or copying of this email is
strictlyprohibited. If you received this email in error, please immediately notify postmaster@fns.ru.</font>