PostgreSQL中如何实现支持动态列的参数化CTE表函数

原生CTE不支持参数化模板定义 #

PostgreSQL 原生 WITH 子句没有自定义参数的语法，你期望的WITH table_function_info($var1) AS (...)这种可复用传参CTE模板无法直接通过原生CTE实现。

符合需求的正确实现方式 #

你需要输出列跟随sales表结构自动变化、无需提前定义返回列的需求，可以通过返回表行类型的SQL函数实现：

方案1：会话级临时函数（单次会话使用，自动销毁不污染全局命名空间） #

CREATE OR REPLACE FUNCTION pg_temp.get_sales_by_website(p_website text)
RETURNS SETOF sales AS $$
SELECT * FROM sales WHERE website = p_website;
$$ LANGUAGE sql STABLE;

调用方式和你的预期逻辑完全匹配：

WITH info AS (
    SELECT * FROM pg_temp.get_sales_by_website('Yahoo')
)
SELECT * FROM info JOIN other USING (id);

也可以直接简化调用，省略CTE层：

SELECT * FROM pg_temp.get_sales_by_website('Yahoo') t
JOIN other USING (id);

方案2：持久化函数（跨会话复用） #

如果需要在多个会话中复用该逻辑，直接创建普通函数即可：

CREATE OR REPLACE FUNCTION get_sales_by_website(p_website text)
RETURNS SETOF sales AS $$
SELECT * FROM sales WHERE website = p_website;
$$ LANGUAGE sql STABLE;

调用方式和临时函数完全一致。

注意事项 #

• 函数定义中RETURNS SETOF sales是直接引用sales表的行类型，只要你修改sales表的字段增减，函数返回的列结构会自动同步，完全满足你不需要提前确定输出列的需求
• 函数标注STABLE关键字可以让PostgreSQL查询优化器对函数调用做优化，提升查询性能
• 如果需要实现更复杂的动态返回列逻辑（非整表返回），可以使用RETURNS SETOF record类型，调用时显式声明返回列结构即可

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