Last night I tore my hair out for about three hours with the following
problem (v8.3.0):
I had a simple scalar query that I wanted to create a function for.
However, when I created the function and called it from another query OR
the PSQL command line, it was two orders of magnitude SLOWER than when I
manually substituted the parameters into the query and ran it directly.
Then I figured out what the cause was:
The manual query was doing an indexed column 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 rewriting the
comparison using a cast of the indexed column. Needless to say, the
does not result in an indexed access (probably the index is searched
sequentially for a match).
I solved the problem by explicitly casting the function parameter to the
type of the index, and that solved the problem.
So, is this the best (or only) way to solve this? I haven't done
exhaustive checking, but it appears that specifying the type of
parameters in the function prototype is only used for type-checking (and
function matching), and no conversion between types is done. Given
that, I'm not sure of the value of the /tablename/./columnname/%TYPE
notation, especially since apparently it can only be used in the
function prototype and not in the body of the function.
If I am wrong on any of the above, I would be pleased to know it.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
