PostgreSQL: get selected rows with given order

Author:Wojciech Muła
Added on:2010-03-30

Suppose that a database stores some kind of a dictionary and an user picks some items, but wants to keep the order. For example the dictionary has entries with id=0..10, and the user picked 9, 2, 4 and 0. This simple query does the job (query splitted):

foo = SELECT (ARRAY[9,2,4,0])[i] AS index, i AS ord FROM generate_series(1, 4) AS i
SELECT * FROM dictionary INNER JOIN (foo) ON ORDER BY foo.ord