"Tomas Vondra" <tv@fuzzy.cz> writes:
> On 6 Září 2011, 0:27, Rory Campbell-Lange wrote:
>> The prepared query runs in almost exactly the same time as the function,
>> but thanks for the suggestion. A very useful aspect of it is that I was
>> able to get the EXPLAIN output which I guess gives a fairly good picture
>> of the plan used for the function.
> Well, my point was that the queries wrapped in functions are executed just
> like prepared statements. And because prepared queries do not use
> parameter values to optimize the plan, the result may be worse compared to
> queries with literal parameters. So I was not expecting an improvement, I
> was merely trying to show the problem.
Right. The actual fix is to use EXECUTE so you force a new plan to be
generated each time. If you use EXECUTE USING to insert the parameter
values, you can avoid most of the notational mess this would otherwise
imply, as well as the risk of SQL-injection bugs from failing to quote
parameter values safely.
I'm hoping that 9.2 will be smart enough to not need such workarounds,
but that's where things stand at the moment.
regards, tom lane