dynamic sorting...

Started by Yavuz Kavusalmost 20 years ago2 messagesgeneral
Jump to latest
#1Yavuz Kavus
yavuzkavus@gmail.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.

#2Florian Pflug
fgp@phlo.org
In reply to: Yavuz Kavus (#1)
Re: dynamic sorting...

Yavuz Kavus wrote:

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

There isn't any other ;-)

The first example works, because you replaced something that
represents a _value_ by a case. But in the second example, you
replaces a _keyword_ by a case, and that won't work.

Even for the first example, using "execute ..." will probably be faster,
at least if tb_test has moderate size. Using "case" in the first example
is bound to confuse the optimizer, and leads potentially bad performance
IMHO.

greetings, Florian Pflug