PostgreSQL11同一查询内对新生成列执行字符串函数的解决方法

PostgreSQL的SQL执行逻辑中，SELECT列表里的别名定义仅在该查询的ORDER BY、DISTINCT等后续阶段生效，同一层级SELECT列表内的后续表达式无法引用前面定义的别名，你在REGEXP_MATCHES里直接用刚定义的id别名，执行时该别名还未完成计算，因此会报列不存在的错误。

你可以通过正则捕获组一次性提取所需字段，无需嵌套多层查询，创建视图的语句如下：

CREATE OR REPLACE VIEW labels_view AS
SELECT
    descr,
    (REGEXP_MATCH(descr, '^(\d+)([A-Za-z]?)_(.*)$'))[1] AS id,
    NULLIF((REGEXP_MATCH(descr, '^(\d+)([A-Za-z]?)_(.*)$'))[2], '') AS rate,
    (REGEXP_MATCH(descr, '^(\d+)([A-Za-z]?)_(.*)$'))[3] AS name
FROM labels;

如果你更偏好分层拆分的写法，也可以用子查询先提取前缀和后缀，再拆分得到id和rate：

CREATE OR REPLACE VIEW labels_view AS
SELECT
    descr,
    REGEXP_REPLACE(prefix, '[^0-9]', '', 'g') AS id,
    REGEXP_REPLACE(prefix, '[0-9]', '', 'g') AS rate,
    suffix AS name
FROM (
    SELECT
        descr,
        SPLIT_PART(descr, '_', 1) AS prefix,
        SPLIT_PART(descr, '_', 2) AS suffix
    FROM labels
) t;

两种写法都能适配你给出的示例数据，创建完成后直接查询labels_view即可获得所需的4个字段。

内容的提问来源于stack exchange，提问作者refriedjello