PostgreSQL:引用WITH创建的临时表的特定第n列

Question

假设我有一个使用WITH子句创建的临时表，如下:

WITH temporary_table AS (
VALUES ('First', 1),
('Second', 2),
('Third', 3))

是否可以参考SELECT子句中的第n列?比如:

WITH temporary_table AS (
VALUES ('First', 1),
('Second', 2),
('Third', 3)
)
SELECT second_column
FROM temporary_table;

如果没有，是否有其他方法可以在具有只读权限的情况下创建一些仅用于查询目的的临时表?

我没有找到任何有用的WITH或SELECT PostgreSQL文档。

Answer 1

只需给它们一个名字，例如作为WITH子句的一部分:

WITH temporary_table (c1, c2) AS (
VALUES ('First', 1),
('Second', 2),
('Third', 3)
)
SELECT c2
FROM temporary_table;

一种稍微复杂一点的方法是命名VALUES子句的列:

WITH temporary_table AS (
select *
from (
VALUES ('First', 1),
('Second', 2),
('Third', 3)  
) as t(c1, c2)
)
SELECT c2
FROM temporary_table;

Answer 2

为列命名

例如:

WITH temporary_table (a, b) AS (
VALUES ('First', 1),
('Second', 2),
('Third', 3)
)
SELECT b
FROM temporary_table;