Last night I tore my hair out for about three hours with the following problem (v8.3.0):<br /><br /> I had a simple
scalarquery that I wanted to create a function for. However, when I created the function and called it from another
queryOR the PSQL command line, it was two orders of magnitude SLOWER than when I manually substituted the parameters
intothe query and ran it directly. Then I figured out what the cause was:<br /><br /> The manual query was doing an
indexedcolumn lookup on the value, a simple text string. However, when the function was passed the text string as the
value,it was comparing the TEXT type of the parameter to the CHAR( n) type of the indexed column, and apparently
rewritingthe comparison using a cast of the indexed column. Needless to say, the does not result in an indexed access
(probablythe index is searched sequentially for a match).<br /><br /> I solved the problem by explicitly casting the
functionparameter to the type of the index, and that solved the problem.<br /><br /> So, is this the best (or only) way
tosolve this? I haven't done exhaustive checking, but it appears that specifying the type of parameters in the
functionprototype is only used for type-checking (and function matching), and no conversion between types is done.
Giventhat, I'm not sure of the value of the <tt class="LITERAL"><tt class="REPLACEABLE"><i>tablename</i></tt>.<tt
class="REPLACEABLE"><i>columnname</i></tt>%TYPE</tt>notation, especially since apparently it can only be used in the
functionprototype and not in the body of the function.<br /><br /> If I am wrong on any of the above, I would be
pleasedto know it.<br /><br /> -- Dean<br /><pre class="moz-signature" cols="72">--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
