Re: ORDER BY with plpgsql parameter

The best of both world (speed and the ability to have different sort
options) that I can think of would be to use IF test with different
queries for the parameter.  That way you get pre-planning and can
specify the sort as a 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?
>
>
>>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?
>
>
>
>>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?
>
>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?
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>