dynamic sorting...

["Yavuz Kavus" <yavuzkavus(at)gmail(dot)com>]

hi everybody.

i have a sp. i am passing two argument to it.
first one : _sort_column --> defines which column will be used in "order by".
second : _sort_direction --> define sorting direction(asc or desc).

i writing sp as :


CREATE OR REPLACE FUNCTION ftest(_sort_column "varchar", _sort_direction "varchar")
  RETURNS refcursor AS
$BODY$
declare
     _result_set refcursor;
begin
     open _result_set for
    select firstname, lastname from tb_test 
    order by 
    case 
        when _sort_column  ilike 'lastname' then lastname
        else firstname
    end;
     return _result_set;
end;$BODY$
  LANGUAGE 'plpgsql';


this works fine, however the next doesnt(i couldnt compile it ) :
CREATE OR REPLACE FUNCTION ftest(_sort_column "varchar", _sort_direction "varchar")
  RETURNS refcursor AS
$BODY$
declare
     _result_set refcursor;
begin
     open _result_set for
    select firstname, lastname from tb_test 
    order by 
    case 
        when _sort_column  ilike 'lastname' then lastname
        else firstname
    end 

    case 

        when _sort_direction  ilike 'asc' then asc

        else desc

    end;
     return _result_set;
end;$BODY$
  LANGUAGE 'plpgsql';

any suggestions to run it? (except EXECUTE SQLSTRING).

thanks a lot.