如何确保PostgreSQL中函数必执行且两个查询独立运行？

这确实是PostgreSQL优化器的一个常见“聪明反被聪明误”的场景——当my_table为空时，优化器判定WHERE identifier IN (...)的结果必然是空集，所以直接跳过了step_1()的执行，导致插入逻辑根本没触发。要同时满足你的两个需求（强制执行step_1()、让插入的行在后续查询中可见），下面是两种可靠的实现方式：

方案一：改用PL/pgSQL编写step_2（推荐） #

PL/pgSQL是过程化语言，它会严格按照语句顺序执行，不会被优化器跳过任何步骤。我们可以先明确执行step_1()，再基于插入的ID查询数据：

CREATE OR REPLACE FUNCTION step_2() RETURNS SETOF my_table AS $$
DECLARE
    inserted_ids uuid[];
BEGIN
    -- 第一步：强制执行step_1，把返回的ID存入变量（确保执行逻辑触发）
    SELECT array_agg(identifier) INTO inserted_ids FROM step_1();
    
    -- 第二步：基于插入的ID查询my_table，此时能看到刚插入的行
    RETURN QUERY SELECT * FROM my_table WHERE identifier = ANY(inserted_ids);
END;
$$ LANGUAGE plpgsql VOLATILE;

为什么这个方案有效： #

• 过程化执行：SELECT ... INTO inserted_ids FROM step_1()会被强制执行，不管my_table初始状态如何；
• 快照一致性：整个函数在同一个事务内执行，step_1插入的行对后续的SELECT完全可见，不需要额外的事务控制。

方案二：在SQL函数中阻止优化器跳过子查询 #

如果你更偏好纯SQL函数，可以通过添加OFFSET 0来干扰优化器的判断，让它必须执行step_1()：

CREATE OR REPLACE FUNCTION step_2() RETURNS SETOF my_table AS $$
SELECT * FROM my_table 
WHERE identifier IN (SELECT * FROM step_1() OFFSET 0);
$$ LANGUAGE sql VOLATILE;

为什么这个方案有效： #

• OFFSET 0是一个无实际效果的语法，但它会让优化器无法预判子查询的结果是否为空（优化器不会深入分析OFFSET 0的无意义性），因此必须执行step_1()来获取结果；
• 同快照可见：整个SQL语句在同一个快照中执行，step_1插入的行会被后续的SELECT捕获到。

补充说明 #

• 事务隔离级别：不管用哪种方案，只要是默认的READ COMMITTED隔离级别（PostgreSQL默认），同一个事务内的修改对后续查询都是可见的；如果是REPEATABLE READ或SERIALIZABLE级别，同样能看到自己事务内的修改，无需额外配置。
• 性能影响：两种方案的性能差异极小，PL/pgSQL方案的可读性和可控性更强，更适合复杂逻辑的扩展。

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