On v7.0.2:<br /><br /> I have a function preferred(text, text). It returns the second argument if the second is not
nullor the first if the second is null.<br /> I understand I can use coalesce, but this is a simple case and not
practicalbut illustrates the point.<br /><br /> If I do select col1, col2, preferred(col1, col2) as col3 col3 only
containsvalues where col2 had a non-null value.<br /><br /> create function preferred(text, text)<br /> returns text<br
/>as '<br /> declare<br /> first alias for $1;<br /> second alias for $2;<br /> begin<br /> if
secondisnull<br /> then<br /> return first;<br /> else<br />
returnsecond;<br /> end if;<br /> end;'<br /> language 'plpgsql';<br /><br /> e.g.<br /><br
/><fontface="Courier, Courier">col1|col2<br /> ----+----<br /> Am | y<br /> Ba |NULL<br /> Ca | t<br /><br
/></font>Iexpect<br /><br /><font face="Courier, Courier">col1|col2|col3<br /> ----+----+-----<br /> Am | y | Amy<br
/> Ba |NULL| Ba<br /> Ca | t | Cat<br /><br /></font>I get <br /><br /><font face="Courier,
Courier">col1|col2|col3<br/> ----+----+-----<br /> Am | y | Amy<br /> Ba |NULL|NULL<br /> Ca | t | Cat<br /><br
/></font>My major question is how to pass NULL values or values that could be potentially NULL into the function and
geta reliable result.<br /><br /> From what I can gather the function only gets called when both values are present and
notwhen any of them are NULL. Is it because there isn't a match for preferred(text, NULL) or is it something else?
<br/><br /><br /> - <br /> - <b><u>Thomas Swan</u></b> <br /> - Graduate Student -
ComputerScience<br /> - The University of Mississippi<br /> - <br /> - "People can be categorized into two fundamental
<br/> - groups, those that divide people into two groups <br /> - and those that don't."