Multiple ordering options

Question

It is very common for a web page to have multiple ordering options for a table. Right now I have a case where there are 12 options (ordenable columns). The easiest (that I know of) way to do it is to build the SQL query concatenating strings. But I'm wondering if it is the best approach. The string concatenation is something like this (python code):

order = {
    1: "c1 desc, c2",
    2: "c2, c3",
    ...
    12: "c10, c9 desc"
    }
...
query = """
select c1, c2
from the_table
order by %(order)s
"""
...
cursor.execute(query, {'order': AsIs(order[order_option])})
...

My alternative solution until now is to place a series of cases in the order by clause:

select c1, c2
from the_table
order by
    case %(order_option)s
        when 1 then array[c1 * -1, c2]
        when 2 then array[c2, c3]
        else [0.0, 0.0]
    end
    ,
    case %(order_option)s
        when 3 then c4
        else ''
    end
    ,
    ...
    ,
    case when %(order_option)s < 1 or %(order_option)s > 12 then c5 end
;

What is the best practice concerning multiple ordering choices? What happens with index utilization in my alternative code?

Answer 1

First of all, @order is not valid PostgreSQL syntax. You probably borrowed the syntax style from MS SQL Server or MySQL. You cannot use variables in a plain SQL query like that.

In PostgreSQL you would probably create a function. You can use variables there, just drop the @.

Sorting by ARRAY is generally rather slow - and not necessary in your case. You could simplify to:

ORDER  BY
       CASE _order
          WHEN 1 THEN c2
          WHEN 2 THEN c3 * -1
          ELSE NULL  -- undefined!
       END
     , c1

However, a CASE expression like this cannot use plain indexes. So, if you are looking for performance, one way (of several) would be a plpgsql function like this:

CREATE OR REPLACE FUNCTION foo(int)
  RETURNS TABLE(c1 int, c2 int) AS
$BODY$
BEGIN

CASE $1
WHEN 1 THEN
    RETURN QUERY
    SELECT t.c1, t.c2
    FROM   tbl t
    ORDER  BY t.c2, t.c1;

WHEN 2 THEN
    RETURN QUERY
    SELECT t.c1, t.c2
    FROM   tbl t
    ORDER  BY t.c3 DESC, t.c1;
ELSE
    RAISE WARNING 'Unexpected parameter: "%"', $1;
END CASE;

END;
$BODY$
  LANGUAGE plpgsql STABLE;

This way, even plain indexes can be used.

If you actually only have two alternatives for ORDER BY, you could also just write two functions.

Create multi-column indexes on (c2, c1) and (c3 DESC, c1) for maximum performance. But be aware that maintaining indexes carries a cost, too, especially if your table sees a lot of write operations.

---

Additional answer for rephrased question

As I said, the CASE construct will not use plain indexes. Indexes on expressions would be an option, but what you have in your example is outside the scope.

So, if you want performance, build the query in your app (your first approach) or write a server side function (possibly with dynamic SQL and EXECUTE) that does something similar inside PostgreSQL. The WHERE clause with a complex CASE statement works, but is slower.