Changing between ORDER BY DESC and ORDER BY ASC

From: William Garrison <postgres(at)mobydisk(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Changing between ORDER BY DESC and ORDER BY ASC
Date: 2008-08-15 17:35:57
Message-ID: 48A5BE7D.50800@mobydisk.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Glyn Astill 2008-08-15 17:44:18 Re: Killing active users
Previous Message Reid Thompson 2008-08-15 16:51:30 Re: Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory