Let's imagine simple situation – you have table of objects (each with id), and you want objects 3, 71, 5 and 16. And in that order!

How to do it?

First, let's create some test data:

CREATE TABLE test_data (
    id       INT4 PRIMARY KEY,
    codename TEXT
);
INSERT INTO test_data ( id, codename )
    SELECT i, 'codename for: ' || i
    FROM generate_series( 1, 100 ) i;

Now. To get these named 4 rows, I could:

SELECT * FROM test_data WHERE id IN (3, 71, 5, 16);
 id |     codename     
----+------------------
  3 | codename FOR: 3
  5 | codename FOR: 5
 16 | codename FOR: 16
 71 | codename FOR: 71
(4 ROWS)

But – you can't add ‘ORDER BY' clause to get them in order specified in “IN ( … )" part.

So, what can we do.

One approach is to use (available from PostgreSQL 8.2) VALUES(). Like this:

SELECT
    t.*
FROM
    test_data t
    JOIN (
        VALUES
            (1, 3),
            (2, 71),
            (3, 5),
            (4, 16)
        ) AS c (ordering, id) ON t.id = c.id
ORDER BY
    c.ordering;
 id |     codename     
----+------------------
  3 | codename FOR: 3
 71 | codename FOR: 71
  5 | codename FOR: 5
 16 | codename FOR: 16
(4 ROWS)

Which works reasonably well, but you have to provide extra data, and format of this data is not so cool.

Luckily – there are other options.

Since 8.4 we have window functions, and unnest(), so we can:

WITH ordered_want AS (
    SELECT
        id,
        ROW_NUMBER() OVER () AS ordering
    FROM
        unnest( '{3,71,5,16}'::INT4[] ) AS id
)
SELECT
    t.*
FROM
    test_data t
    JOIN ordered_want o ON t.id = o.id
ORDER BY
    o.ordering;

That's much better. Not only you don't have to provide ordering from application, but also delivering the ids becomes much easier – you just pass array of integers, and you're done.

There is also one more option. If you can't use window functions, you can use relatively old trick with ordering by position. Like this:

SELECT
    t.*
FROM
    test_data t
WHERE
    t.id IN (3,71,5,16)
ORDER BY
    POSITION( ',' || t.id || ',' IN ',3,71,5,16,' );

Just be sure to include leading and trailing commas in final string, and add them as well around t.id in order by clause.

Of course this method of sorting gets difficult when you're operating on textual ids, but it's also possible.