Can PostgreSQL use FOR LOOP in ORDER BY?

Question

Can I make SQL statements like this?

    SELECT     
        ARRAY[array]
    FROM   table1
    ORDER BY 
       FOR i in array_length(array,1) LOOP 
                array[i]::numeric
            END LOOP; 

The result I want is:

SELECT     
   ARRAY[array]
FROM   table1
ORDER BY array[1]::numeric, array[2]::numeric, ...

Can I? :)

Answer 1

No, there is no LOOP in SQL. It's an element of procedural languages like PL/pgSQL, though. See:

• Postgres FOR LOOP

But there is a simpler way:

SELECT ...
FROM   table1
ORDER  BY array_column::numeric[];

Arrays values are sorted by element values left-to-right out of the box.

Requirements:

1. Column array_column must be an array with an element type that has a registered cast to numeric. Like text or varchar or some others. Else, you may be able to use text[] as stepping stone: array_column::text[]:numeric[].

2. Each element can be projected to a legal numeric value.