From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Yavuz Kavus <yavuzkavus(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: dynamic sorting... |
Date: | 2006-06-20 12:20:31 |
Message-ID: | 4497E80F.8060500@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Essam Mansour | 2006-06-20 13:17:28 | XML Support |
Previous Message | John DeSoi | 2006-06-20 12:17:11 | Re: psql for winxp? |