PostgreSQL如何在自定义函数内调用WITH子句创建的CTE表

WITH子句创建的公共表表达式（CTE）的作用域仅局限于当前主查询的执行上下文，PL/pgSQL函数是独立的执行单元，默认无法直接访问主查询中的CTE，因此你的原代码运行会抛出relation "session_data" does not exist的错误。

方案1：将CTE数据作为参数传入函数（最推荐） #

该方案性能最优、无额外副作用，不需要修改原有业务逻辑，是最优选择。

• 如果CTE输出为单值/少量固定字段，直接传入对应类型参数即可，示例代码如下：

修改后的函数 #

CREATE FUNCTION my_wonderful_function(input_ntimes int)
RETURNS TABLE (xtimes int)
AS
$$
BEGIN
      RETURN SELECT input_ntimes;
END;
$$ LANGUAGE plpgsql;

修改后的主查询 #

WITH RECURSIVE session_data AS (
      SELECT * FROM (VALUES(1)) t(ntimes)
) SELECT * FROM my_wonderful_function((SELECT ntimes FROM session_data));

• 如果CTE是多行多列的结果集，可以先将结果聚合为数组、JSON等结构化类型再传入，示例如下：

-- 先定义和CTE结构匹配的复合类型
CREATE TYPE session_data_struct AS (ntimes int);

-- 函数接收复合类型数组参数
CREATE FUNCTION my_wonderful_function(input_data session_data_struct[])
RETURNS TABLE (xtimes int)
AS
$$
BEGIN
      RETURN SELECT ntimes FROM unnest(input_data);
END;
$$ LANGUAGE plpgsql;

-- 主查询将CTE结果聚合后传参
WITH RECURSIVE session_data AS (
      SELECT * FROM (VALUES(1), (2), (3)) t(ntimes)
) SELECT * FROM my_wonderful_function((SELECT array_agg(session_data::session_data_struct) FROM session_data));

方案2：临时表中转数据（适合数据量较大的场景） #

如果CTE数据量很大，传参成本过高，可以用会话临时表中转数据，临时表会在会话结束/事务提交后自动销毁，不会持久化占用资源：

-- 第一步：将CTE结果写入临时表
CREATE TEMP TABLE temp_session_data ON COMMIT DROP AS
WITH RECURSIVE session_data AS (
      SELECT * FROM (VALUES(1)) t(ntimes)
) SELECT * FROM session_data;

-- 第二步：修改函数直接访问临时表
CREATE OR REPLACE FUNCTION my_wonderful_function()
RETURNS TABLE (xtimes int)
AS
$$
BEGIN
      RETURN SELECT ntimes FROM temp_session_data;
END;
$$ LANGUAGE plpgsql;

-- 第三步：调用函数
SELECT * FROM my_wonderful_function();

方案3：CTE逻辑内置到函数（适合CTE逻辑固定的场景） #

如果你的CTE逻辑不会频繁变动，可以直接把CTE逻辑写到函数内部，无需修改主查询：

CREATE OR REPLACE FUNCTION my_wonderful_function()
RETURNS TABLE (xtimes int)
AS
$$
BEGIN
      RETURN QUERY
      WITH RECURSIVE session_data AS (
          SELECT * FROM (VALUES(1)) t(ntimes)
      ) SELECT ntimes FROM session_data;
END;
$$ LANGUAGE plpgsql;

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