postgres `order by` argument type

Question

What is the argument type for the order by clause in Postgresql?

I came across a very strange behaviour (using Postgresql 9.5). Namely, the query

select * from unnest(array[1,4,3,2]) as x order by 1;

produces 1,2,3,4 as expected. However the query

select * from unnest(array[1,4,3,2]) as x order by 1::int;

produces 1,4,3,2, which seems strange. Similarly, whenever I replace 1::int with whatever function (e.g. greatest(0,1)) or even case operator, the results are unordered (on the contrary to what I would expect).

So which type should an argument of order by have, and how do I get the expected behaviour?

Answer 1

This is expected (and documented) behaviour:

A sort_expression can also be the column label or number of an output column

So the expression:

order by 1

sorts by the first column of the result set (as defined by the SQL standard)

However the expression:

order by 1::int

sorts by the constant value 1, it's essentially the same as:

order by 'foo'

By using a constant value for the order by all rows have the same sort value and thus aren't really sorted.

To sort by an expression, just use that:

order by 
    case 
       when some_column = 'foo' then 1
       when some_column = 'bar' then 2
       else 3
    end

The above sorts the result based on the result of the case expression.

Answer 2

Actually I have a function with an integer argument which indicates the column to be used in the order by clause.

In a case when all columns are of the same type, this can work: :

SELECT ....
ORDER BY 
  CASE function_to_get_a_column_number()
    WHEN 1 THEN column1
    WHEN 2 THEN column2
    .....
    WHEN 1235 THEN column1235
END

If columns are of different types, you can try:

SELECT ....
ORDER BY 
  CASE function_to_get_a_column_number()
    WHEN 1 THEN column1::varchar
    WHEN 2 THEN column2::varchar
    .....
    WHEN 1235 THEN column1235::varchar
END

But these "workarounds" are horrible. You need some other approach than the function returning a column number. Maybe a dynamic SQL ?