dynamic sorting...

Поиск
Список
Период
Сортировка
От Yavuz Kavus
Тема dynamic sorting...
Дата
Msg-id 95b926fc0606200009u5a8f731awc6f22d9f46e40de9@mail.gmail.com
обсуждение исходный текст
Ответы Re: dynamic sorting...  ("Florian G. Pflug" <fgp@phlo.org>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Trigve Siver
Дата:
Сообщение: Fwd: Re: problem connecting to server
Следующее
От: Ron Johnson
Дата:
Сообщение: Re: Database Clustering on multiple harddisk