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).
