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.
