Re: dynamic sorting...

["Florian G. Pflug" <fgp(at)phlo(dot)org>]

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