PostgreSQL crosstab使用CTE时报tmp_kv关系不存在如何解决

问题现象 #

在PostgreSQL中使用crosstab函数实现数据透视时，在crosstab的第二个查询参数中引用第一个查询内定义的CTEtmp_kv，触发错误：relation "tmp_kv" does not exist。
生产环境为只读副本架构，无创建临时表权限，要求仅通过CTE实现功能。
原问题代码如下：

select * 
from crosstab (
$$  
    with tmp_kv as (
        select distinct pat_id  
            ,col.name as key, replace(replace(replace(value, '[',''), ']', ''),'"','') as value
        from (
            select p.Id as pat_id, nullif(kv.key,'undefined')::int as key, trim(kv.value::text,'"') as value
            from pat_table p 
            left join e_table e on e.pat_id = p.id and e.id is null
            ,jsonb_each_text(p.data) as kv
        ) t 
        left join lateral (
            select name::text as name from public.config_fields fld 
            where id = t.key 
        ) col on true
    )
    select pat_id, key, value 
    from tmp_kv 
    where nullif(trim(key),'') is not null
    order by pat_id, key 
$$,$$
    select distinct key from tmp_kv -- 此处触发tmp_kv不存在错误
    where nullif(trim(key),'') is not null
    order by 1  
$$
) as (
    pat_id bigint
    ...
    ...
);

错误原因 #

crosstab接收的两个SQL字符串参数是独立解析、独立执行的，作用域完全隔离。第一个参数内定义的CTE仅在第一个查询内部生效，第二个查询无法访问该作用域内的对象，因此报表不存在错误。

解决方案 #

将tmp_kv的CTE逻辑定义在crosstab调用的外层，两个传入crosstab的查询都可以访问外层作用域的CTE，同时添加MATERIALIZED关键字保证CTE结果只计算一次，性能与临时表一致，且完全兼容只读副本环境（无需写权限、无需创建实体表）。
修正后的代码如下：

with tmp_kv as MATERIALIZED (
    select distinct pat_id  
        ,col.name as key, replace(replace(replace(value, '[',''), ']', ''),'"','') as value
    from (
        select p.Id as pat_id, nullif(kv.key,'undefined')::int as key, trim(kv.value::text,'"') as value
        from pat_table p 
        left join e_table e on e.pat_id = p.id and e.id is null
        ,jsonb_each_text(p.data) as kv
    ) t 
    left join lateral (
        select name::text as name from public.config_fields fld 
        where id = t.key 
    ) col on true
)
select * 
from crosstab (
$$  
    select pat_id, key, value 
    from tmp_kv 
    where nullif(trim(key),'') is not null
    order by pat_id, key 
$$,$$
    select distinct key from tmp_kv
    where nullif(trim(key),'') is not null
    order by 1  
$$
) as (
    pat_id bigint
    -- 此处按实际透视生成的列补全字段定义即可
    -- 示例：col1 text, col2 numeric ...
);

补充说明 #

• 如果使用PostgreSQL 11及更早版本，CTE默认就是物化执行的，可以省略MATERIALIZED关键字。
• 不推荐在crosstab的两个参数内分别重复写相同的CTE逻辑，这种写法会导致相同计算执行两次，性能损耗明显。

注：MATERIALIZED CTE的结果存储在当前查询的临时缓存中，查询结束后自动释放，不需要任何建表、写数据权限，完全满足只读副本的部署要求。

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