dynamic sorting...

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.

Responses

Browse pgsql-general by date

  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