Changing between ORDER BY DESC and ORDER BY ASC

On Aug 15, 2008, at 12:35 PM, William Garrison wrote:

Is there an easy way to write one single query that can alternate
between ASC and DESC orders? Ex:

CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count
integer, _sortDesc boolean)
RETURNS SETOF text AS
$BODY$
SELECT
something
FROM
whatever
WHERE
whatever
ORDER BY
another_column
OFFSET $1 LIMIT $2
($4 = true ? 'DESC' : 'ASC');
$BODY$
LANGUAGE 'sql' VOLATILE;

I can think of a few ways, but I am hoping for something more elegant.
1) In my case another_column is numeric, so I could multiple by
negative one if I want it in the other order. Not sure what this
does to the optimizer if the column is indexed or not.

In my experience, it's pretty rare for an index to be used to satisfy
an ORDER BY.

2) I could write the statement twice, once with ASC and once with
DESC, and then use IF/ELSE structure to pick one.
3) I could generate the statement dynamically.

I am hoping there is some super secret extension that can handle
this. This seems like one of those foolish things in SQL, where it
is too declarative. ASC and DESC should be parameters to order by,
not a part of the syntax. But I digress... any other suggestions?

None that I can think of, unfortunately. It might not be horribly
hard to allow plpgsql to use a variable for ASC vs DESC; that might
be your best bet.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On Fri, Aug 15, 2008 at 9:35 PM, William Garrison <postgres@mobydisk.com>wrote:

Is there an easy way to write one single query that can alternate between
ASC and DESC orders? Ex:

Take a look at this link
http://www.mail-archive.com/pgsql-general@postgresql.org/msg111788.html

--
Regards,
Sergey Konoplev

http://www.mail-archive.com/pgsql-general@postgresql.org/msg111788.htmlprobably
won't match an index, because ASC or DESC ordering depends NOT on
the table's data, but on the function parameter.

Unfortunately the planner does not recognize the following case:

CREATE TABLE "public"."prime" (
"num" NUMERIC NOT NULL,
CONSTRAINT "prime_pkey" PRIMARY KEY("num")
) WITH OIDS;

CREATE INDEX "prime_idx" ON "public"."prime"
USING btree ((CASE WHEN true THEN num ELSE (- num) END));

CREATE OR REPLACE FUNCTION "public"."prime_test" (a boolean) RETURNS SETOF
integer AS
$body$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
select *
from prime
order by case when a then num else -num end
limit 20
LOOP
RETURN NEXT rec.num;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

EXPLAIN ANALYZE select * from prime_test(true);
-- hundreds of seconds - so the index is not used

Seems the planner does not understand that "a" variable is constant "true"
within the query and does not use prime_idx index (in spite of prime_idx is
defined dummyly as CASE WHEN true THEN ... ELSE ... END).

William, you may try to use EXECUTE instruction with customly built query
with ASC or DESC inserted.

On Mon, Aug 18, 2008 at 3:31 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: