dynamic sorting...
От | Yavuz Kavus |
---|---|
Тема | dynamic sorting... |
Дата | |
Msg-id | 95b926fc0606200009u5a8f731awc6f22d9f46e40de9@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: dynamic sorting...
|
Список | 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.
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 по дате отправления: