Re: ORDER BY with plpgsql parameter

Thomas Schoen wrote:
>>You need to use the FOR-IN-EXECUTE style of query. That way
>>you can use any string you want (including text passed in as
>>a parameter) to build the query inside the function ...
>
>
> that is what i want to avoid. (i wrote that in my first mail)
> My question was about why it is not possible to do it like this:
> ....ORDER BY $1
> I don't know if it is standards-conform or if someone considers it a usefull
> feature, but i think it would help users to write capsulated
> statistics-functions or simply browsable and orderable lists.

You want to build a dynamic query (sorted in different ways depending on
a function parameter).

You don't want to use the dynamic query statement (EXECUTE).

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.

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.

--
Richard Huxton
Archonet Ltd