Thread: ORDER BY with plpgsql parameter
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
>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?
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 > >
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.
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.