Re: ORDER BY with plpgsql parameter - Mailing list pgsql-general

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