467,892 Members | 1,850 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,892 developers. It's quick & easy.

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 recIN
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 ma*******@postgresql.org)

Nov 23 '05 #1
  • viewed: 4365
Share:
1 Reply
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.******@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 ma*******@postgresql.org)


__________________________________________________ ____________________

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bill Nedell | last post: by
2 posts views Thread by Miso Hlavac | last post: by
1 post views Thread by Rajesh Kumar Mallah | last post: by
4 posts views Thread by Bill Moran | last post: by
4 posts views Thread by Markus Bertheau | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.