Postgres subquery, ordering by subquery

Question

If I have a query such as :

select * from tbl where id in (10, 20, 9, 4);

the results returned could potentially be in this order: 4, 9, 10, 20

but what if was looking to maintain the order of the list passed into the initial query? how would you approach this?

Ultimately I'm using Django as the ORM for my app here but I'm looking to investigate what is feasible at db level first.

Any ideas most welcome!

Answer 1

Using CASE isn't very portable because you have to know ahead of time all of the values and the order that you want to sort. The cleanest way is to use an array for the values and sort by the item's index in the array.

I had posted this to the Postgres Snippet Library a while back.

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
  RETURNS int AS 
$$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$$ LANGUAGE sql IMMUTABLE;

SELECT * FROM foo ORDER BY idx(array['Freshman','Sophomore','Junior','Senior'], foo.grade_level)