ORDER BY with plpgsql parameter - Mailing list pgsql-general

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