[SOLVED] PostgreSQL - why can't I ORDER BY a function of a column?

PostgreSQL - why can't I ORDER BY a function of a column?

I'm trying to get something I thought should be relatively simple (it works in Oracle and MySQL). The PostgreSQL fiddle for the code below is available here - just change the server to check out the others.

Very simple test case:

CREATE TABLE x
(
  y CHAR(1)
);

populate:

INSERT INTO x VALUES ('x');

and

INSERT INTO x VALUES('y');

then (works - as one would expect):

SELECT
  y AS the_char
FROM 
  x
ORDER BY the_char;

Result:

the_char
       x
       y

But then, if I try the following:

SELECT
  y AS the_char
FROM 
  x
ORDER BY ASCII(the_char);

I receive an error:

ERROR:  column "the_char" does not exist
LINE 5: ORDER BY ASCII(the_char);

As mentioned, this works with Oracle and MySQL, but not on PostgreSQL, Firebird and SQL Server.

Can anyone explain why? What is it about a simple function of the column that causes the ORDER BY to fail? This seems to conflict with the manual here which says:

The sort expression(s) can be any expression that would be valid in the query's select list. An example is:

SELECT a, b FROM table1 ORDER BY a + b, c;

Solution

Only input columns can be used in expressions other than simple column names, as specified in the documentation:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.