> gears=> select *, Array[4,5] as array_from_select from foo order by > array_from_select[1]; > > ERROR: column "array_from_select" does not exist
array_from_select is not a column name, it is an alias. The ways in which an alias can be used in a query are somewhat limited -- you can order by the alias of a result column, but you can't use the alias in arbitrary expressions.
You could do something like this:
test=# with x as (select *, Array[4,5] as array_from_select from foo) test-# select * from x order by array_from_select[1]; value | array_from_select -------+------------------- 1 | {4,5} 2 | {4,5} (2 rows)
In this case array_from_select becomes a column in the result set of common table expression (CTE) x and *can* be used in expressions.