Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice?

It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.

Let's say we have an application that lists pre-owned cars (à la CraigsList). The list of cars can be sorted by price or colour. we have one function that, given a certain amount of parameters (let's say price-range, colour, and sorting criteria for instance) returns a set of records with the results.

To make it concrete, let's assume cars are all in the following table:

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

The table would have indices for most columns...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

And have some commodity enumerations:

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

... and some sample data

```
INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES
('Ford', 'Mondeo', 1990, 2000.00, 'green'),
('Audi', 'A3', 2005, 2500.00, 'golden magenta'),
('Seat', 'Ibiza', 2012, 12500.00, 'dark blue'),
('Fiat', 'Punto', 2014, NULL, 'yellow'),
('Fiat', '500', 2010, 7500.00, 'blueish'),
('Toyota', 'Avensis', NULL, 9500.00, 'brown'),
('Lexus', 'CT200h', 2012, 12500.00, 'dark whitish'),
('Lexus', 'NX300h', 2013, 22500.0

Three points that I would raise,

• This is a very basic query, even in your applied version. Create a VIEW for it. Have your users customize the WHERE conditions using the VIEW. Functions are black boxes to the query planner. It's horrible to use them inside of other functions, only SQL is inlined. And, dynamic functions do not get cached plans.

• If you want to continue to use plpgsql, use RETURNS QUERY (or RETURNS QUERY EXECUTE) not RETURNS SETOF. There is no reason to use RETURNS SETOF with a sort. It has to be buffered anyway, afaik. You will encounter problems with either one if your resultset is greater than work_mem.

• I'm not sure what you're app is written in. I assume web. I was in the automotive industry for a decade though, made lots of things like Craigslist and posting tools for Craigslist. "Don't sort stuff for users in the database" is generally a good rule of thumb. There is no reason for it. Drop that stuff out in JSON, and let them handle it in the browser. Unless you're showing more than 1000 rows, it's not even worth thinking about. Just consider the round trip time from a cell phone tower. You'll never get that time back wondering about this problem.

Moving forward, I'd even consider wrapping a service like PostgREST which handles thoroughly arbitrary ordering,

If you care where nulls are sorted, add nullsfirst or nullslast:

GET /people?order=age.nullsfirst
GET /people?order=age.desc.nullslast

StackExchange Database Administrators Q#160237, answer score: 3