Re: ORDER BY with plpgsql parameter

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