无法在Postgres交叉表查询中使用公用表表达式
问题描述:
我试图使用Postgres的tablefunc
扩展的CROSSTAB
函数对某些数据执行一个数据透视操作。数据首先需要进行一些转换,我在公用表表达式中执行了一些转换。无法在Postgres交叉表查询中使用公用表表达式
但是,看起来CROSSTAB
看不到这些表达式的结果。
例如,来自一个临时表这个查询的采购数据正常工作:
CREATE TEMPORARY TABLE
temporary_table
(name, category, category_value)
ON COMMIT DROP
AS (
VALUES
('A', 'foo', 1 ),
('A', 'bar', 2 ),
('B', 'foo', 3 ),
('B', 'bar', 4 )
);
SELECT * FROM
CROSSTAB(
'SELECT * FROM temporary_table',
$$
VALUES
('foo'),
('bar')
$$
) AS (
name TEXT,
foo INT,
bar INT
);
,正如预期的,产生以下输出:
name | foo | bar
text | integer | integer
---- | ------- | -------
A | 1 | 2
B | 3 | 4
但相同的查询,这时间使用公用表表达式不运行:
WITH
common_table
(name, category, category_value)
AS (
VALUES
('A', 'foo', 1 ),
('A', 'bar', 2 ),
('B', 'foo', 3 ),
('B', 'bar', 4 )
)
SELECT * FROM
CROSSTAB(
'SELECT * FROM common_table',
$$
VALUES
('foo'),
('bar')
$$
) AS (
name TEXT,
foo INT,
bar INT
)
,并产生以下错误:
ERROR: relation "common_table" does not exist
LINE 1: SELECT * FROM common_table
^
QUERY: SELECT * FROM common_table
********** Error **********
ERROR: relation "common_table" does not exist
SQL state: 42P01
我把它这意味着文本查询(SELECT * FROM common_table
)运行在某种不同的环境呢?
注:的tablefunc
扩展名必须是启用CROSSTAB
可用:
CREATE EXTENSION IF NOT EXISTS tablefunc;
答
所有你需要做的就是将你的CTE
里面的字符串作为第一个参数crosstab(text, text)
函数只是就像你对select语句所做的一样。它将被正确解析和执行。这是因为您提供了完整的SQL语句,可以在第一个参数中生成源代码集。
你需要加倍您的引号里面的字符串或者使用美元符$$
就像你用第二个参数,我做如下:
SELECT * FROM
CROSSTAB(
$$
WITH common_table(name, category, category_value) AS (
VALUES
('A', 'foo', 1 ),
('A', 'bar', 2 ),
('B', 'foo', 3 ),
('B', 'bar', 4 )
)
SELECT * FROM common_table $$,
$$
VALUES
('foo'),
('bar')
$$
) AS (
name TEXT,
foo INT,
bar INT
);
结果
name | foo | bar
------+-----+-----
A | 1 | 2
B | 3 | 4