Thomas Schoen wrote:
>>You want to build a dynamic query (sorted in different ways depending on
>>a function parameter).
>
> yes.
>
>>You don't want to use the dynamic query statement (EXECUTE).
>
> yes, because it seems to me, that "for in execute" is slower than the direct
> way. Is that right?
Sometimes yes, sometimes no.
>>The whole point of plpgsql is that the queries can be compiled and
>>pre-planned. If you want to change the sorting then that implies a
>>different plan, which implies using the dynamic query feature.
>
> OK, does that mean, that it is no difference in performance whether i use "FOR
> IN EXECUTE" or two different functions with different sorting?
I would expect two functions, each with a simple static query to be
faster. Of course, if you want to sort by a dozen different columns
separate functions may be too much trouble.
>>What you want to do is possible if you use one of the interpreted
>>languages, e.g. pltcl/plperl (plphp?). Of course, that means none of
>>your query plans get compiled.
>
> What happens to the query plan if i use function-parameters in the where
> clause of my statement? Is the function recompiled then?
If I understand you correctly, no. If you have a query like:
SELECT * FROM table1 WHERE c < $1
At planning time, it doesn't know what value $1 will have, so it doesn't
know whether there will be few or many results returned. So, it makes a
best guess and that plan will be used every time you call the function.
The advantage to this is that for a complicated query with no or few
parameters, you don't have to re-plan the query every time you call the
function.
> Conclusion: if i want to sort inside the functions depending on function
> parameters, the best way to do it is using "FOR IN EXECUTE"??
> Is that right?
For plpgsql, yes.
--
Richard Huxton
Archonet Ltd
