Postgres subquery, sorting by subquery

Question

If I have a request, for example:

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

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

but what if it was necessary to preserve the order of the list passed to the original request? how would you approach this?

I end up using Django as an ORM for my application, but I'm looking to learn what is possible at the db level in the first place.

Any ideas are welcome!

Answer 1

Using CASE is not very portable, because you need to know all the values ​​and the order you want to sort in advance. The cleanest way is to use an array for the values ​​and sort by the index of the element in the array.

Postgres .

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 ( ['Freshman', 'Sophomore', 'Junior', 'Senior'], foo.grade_level)

Answer 2

, CASE ORDER BY

Answer 3

, , . !