Postgres13.4的SELECT查询CTE中调用插入函数报错怎么解决？

问题根因 #

你遇到的问题包含两个独立的底层原因：

1. 参数类型不匹配：你定义的dba.event_log_add接收两个citext类型参数，但调用时传入的第一个参数是无类型字符串字面量，第二个参数是||拼接生成的text类型，PostgreSQL无法自动匹配到对应函数签名，所以抛出类型不存在的错误。
2. CTE执行规则差异：PostgreSQL对可写CTE（直接包含INSERT/UPDATE/DELETE的CTE）有特殊规则：无论该CTE是否被外层查询引用，都会强制执行，因为可写语句有明确副作用。但你把INSERT封装到函数中后，优化器无法识别普通SELECT 函数()调用的副作用，只要CTE没有被外层查询引用，就会被优化器直接裁剪跳过，导致不报错也没有数据插入。

正确实现方式 #

只需要同时解决类型匹配和CTE引用两个问题即可，示例代码如下：

WITH
values_cte AS (
 SELECT clock_timestamp() AS ct
),
log AS (
 -- 显式转换参数为citext类型匹配函数签名
 SELECT dba.event_log_add(
  'CTE event_log_add check'::citext,
  ('clock = ' || ct::text)::citext
 ) AS exec_result
 FROM values_cte
)
-- 外层查询同时引用两个CTE，保证log CTE不会被裁剪
SELECT values_cte.* FROM values_cte, log;

如果希望进一步简化调用，也可以修改函数定义，让它自动兼容text类型入参，避免每次显式转换：

DROP FUNCTION IF EXISTS dba.event_log_add(text, text);
CREATE FUNCTION dba.event_log_add(
   name_in        text,
   description_in text
)
RETURNS int4
LANGUAGE sql
VOLATILE -- 显式标记函数为易变、有副作用
PARALLEL UNSAFE
AS
$BODY$
INSERT INTO dba.event_log (name, details)
VALUES (name_in::citext, description_in::citext)
RETURNING 1;
$BODY$;

修改后调用时不需要再手动转换参数类型，只要保证外层引用log CTE即可。

内容的提问来源于stack exchange，提问作者Morris de Oryx