From: | "Yavuz Kavus" <yavuzkavus(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | dynamic sorting... |
Date: | 2006-06-20 07:09:23 |
Message-ID: | 95b926fc0606200009u5a8f731awc6f22d9f46e40de9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Golovanov | 2006-06-20 07:29:42 | DocBook 4.2 detecting at configure time |
Previous Message | Ron Johnson | 2006-06-20 07:08:33 | Re: Database Clustering on multiple harddisk |