ORDER BY with plpgsql parameter

is it possible to use a parameter of a plpgsql-function to order a
selection
inside the function?

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 ...

http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html
( it is the very last thing on that page )

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

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.

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

I don't know if it can help but this is how I do it with SQL Server 2000:

CREATE PROCEDURE dbo.cbi_carregaDiretorios @equipe varchar( 20 ), @ordem char(
4 ) = 'nome' AS

select cad.cd_cnpjInt as cnpj, cad.nm_nome as nome
from dbSup001.dbo.sup_cad_instituicao as cad
inner join dbSup001.dbo.sup_cod_equipe as equipe on cad.cd_codEquipeDir =
equipe.cd_equipe
inner join dbCBI001.dbo.cbi_instituicoes as listaInst on listaInst.cnpj =
cad.cd_cnpjInt
where rtrim( equipe.ds_unidade + equipe.ds_subunidade + equipe.ds_equipe ) like
'%' + rtrim( @equipe ) + '%'
order by
case when @ordem = 'nome' then cad.nm_nome end,
case when @ordem = 'cnpj' then cnpj end
GO

The trick with SQL Server is that you have to use one case to each possible
column to be ordered.

Hope this helps.
Regards,
Clodoaldo Pinto

 --- Thomas Schoen <t.schoen@vitrado.de> escreveu: > Hi,

is it possible to use a parameter of a plpgsql-function to order a selection
inside the function?
What i would like to do is pass a column-name/alias-name to a plpgsql
function
and use the variable as order-by-parameter like this:

create function foo(varchar)
RETURNS SETOF test
AS '
declare
rec test%ROWTYPE;
begin
FOR rec IN
SELECT *
FROM test
ORDER BY $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
end;
' LANGUAGE 'plpgsql';

This is only the core of what my function should do but can hopefully
describe
what my question is about.
This function worked without error but did not sort anything.

I tried several solutions inside this function, e.g.:

SELECT *
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN foo
WHEN $1 = ''bar'' THEN bar
END

wich throws the exception
"ERROR: CASE types character varying and integer cannot be matched"
where "foo" is of type varchar and "bar" is of type int4.

Since i read in the docu, that "ORDER BY" accepts the numer of the column to
sort by, i tried:

SELECT *
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN 1
WHEN $1 = ''bar'' THEN 2
END

This worked without exception but did not sort either.

I tried another one:

SELECT
foo AS col1
bar AS col2
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN col1
WHEN $1 = ''bar'' THEN col2
END

This throws
"ERROR: column "col1" does not exist.
Normal SQL-statements accept column-aliases as ORDER BY - criterium but
inside
a CASE it does not seem to work.

It seems that with version 7.4 it is not possible to do what i tried.
Maybe some of the hackers want to put that on their list of feature requests
if they consider it a usefull feature.
It would be great to simply write it like this:
.... ORDER BY $1

I know that i can put the "ORDER BY" clause outside within the function call
(SELECT * FROM test('foo') ORDER BY foo) but since i want to use it together
with LIMIT AND OFFSET i don't want the function to return all rows and sort
it afterwards, because the count of rows returned would decrease the
functions performance enormously.

If someone knows a better solution i would be very gratefull.
Maybe a really have to build the statement as text and use it in a "FOR rec
IN
EXECUTE". I hoped to find a usefull workaround for that hack.

best regards, tom sch�n

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

______________________________________________________________________

Participe da pesquisa global sobre o Yahoo! Mail:
http://br.surveys.yahoo.com/global_mail_survey_br

On Wed, 2 Jun 2004, 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

One problem is that doing the above as column name would make the $1 have
a different meaning from its use in other places like where clauses (where
it acts like a bound parameter).

Apart from the quoting issue, I'm also not sure how it would be any
different from for-in-execute in any case.

On Wed, 2 Jun 2004, 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

One problem is that doing the above as column name would make the $1 have
a different meaning from its use in other places like where clauses (where
it acts like a bound parameter).

I know what you mean.
I'm aware of this problem. I tired to quote the parameter using quote_ident
functions which did not work either.
I do not unserstand the logic behind parameters used in plpgsql-functions.
I don't know how they are bound inside the database-logic.

Apart from the quoting issue, I'm also not sure how it would be any
different from for-in-execute in any case.

Maybe thats just my preference.
I don't like the "build a string to interpret" kind of code.
But it would be interesting to know if their are any performance disadvantages
when using the "for in execute".
My experience of using "for in execute" was so far that it is much slower than
doing it the direct way - and way slower than using sql-functions instead of
plpgsql-functions.
Maybe that experience was subjective.

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?

On Wed, 2 Jun 2004, Thomas Schoen wrote:

On Wed, 2 Jun 2004, 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

One problem is that doing the above as column name would make the $1 have
a different meaning from its use in other places like where clauses (where
it acts like a bound parameter).

I know what you mean.
I'm aware of this problem. I tired to quote the parameter using quote_ident
functions which did not work either.
I do not unserstand the logic behind parameters used in plpgsql-functions.
I don't know how they are bound inside the database-logic.

AFAIK it's similar to PREPARE/EXECUTE. You have a number of arguments
whose values are substituted in as literals in those positions.

Apart from the quoting issue, I'm also not sure how it would be any
different from for-in-execute in any case.

Maybe thats just my preference.
I don't like the "build a string to interpret" kind of code.
But it would be interesting to know if their are any performance disadvantages
when using the "for in execute".

It does replan the query. But you would need to presumably replan if you
were changing the order by column anyway so I'm not sure that allowing
order by $1 to mean order by an expression stored as text in $1 would
realistically have any different performance characteristics than the
execute case.

My experience of using "for in execute" was so far that it is much slower than
doing it the direct way - and way slower than using sql-functions instead of
plpgsql-functions.
Maybe that experience was subjective.

I've seen it both ways, it depends on alot of factors.

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:

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

Thank you all for your detailed answers!

My conclusion is:
- Use "FOR IN EXECUTE" if you want to sort by many different columns depending
on the function paramaters.
- Use "IF" and a seperate SQL-statement for every sort-column provided by the
function parameter if you only want to sort by a few different columns and
want to optimize the performance of your function.