sql - PostgreSQL - 为什么我不能按列的函数进行排序？

Question

我正在尝试获得一些我认为应该相对简单的东西(它适用于 Oracle 和 MySQL)。以下代码的 PostgreSQL fiddle 可用 here - 只需更改服务器即可查看其他服务器。

非常简单的测试用例:

CREATE TABLE x
(
  y CHAR(1)
);

填充:

INSERT INTO x VALUES ('x'); 

和

INSERT INTO x VALUES('y');

然后(工作 - 正如人们所期望的那样):

SELECT
  y AS the_char
FROM 
  x
ORDER BY the_char;

结果:

the_char
       x
       y

但是，如果我尝试以下操作:

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

我收到一个错误:

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

如前所述，这适用于 Oracle 和 MySQL，但不适用于 PostgreSQL、Firebird 和 SQL Server。

谁能解释一下为什么？导致 ORDER BY 失败的列的简单函数是什么？这似乎与手册冲突 here其中说:

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

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

Answer 1

在表达式中只能使用输入列而不是简单的列名，如 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.

另请阅读 this note:

In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.