Re: order by using functions under unions

pgsql-bugs(at)postgresql(dot)org writes:
> select name from temp where name like 'e%'
> union
> select name from temp where name not like 'e%'
> order by substr(name,1,4) asc;

This isn't supported. 7.1 knows that it can't do it:

regression=# select name from temp where name like 'e%'
regression-# union
regression-# select name from temp where name not like 'e%'
regression-# order by substr(name,1,4) asc;
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns

It'd be nice to make it happen for the case you illustrate (ORDER BY on
an expression using only result columns) but that's not done yet.

In the meantime you can work around it (again, in 7.1) by using an
explicit subselect:

regression=# select name from (
regression(# select name from temp where name like 'e%'
regression(# union
regression(# select name from temp where name not like 'e%'
regression(# ) ss
regression=# order by substr(name,1,4) asc;
name
---------
chantal
daniel
eric
ernst
(4 rows)

regards, tom lane