PostgreSQL自定义函数在WHERE子句中调用返回行数异常

核心原因是你定义的是VOLATILE（易失）函数，在WHERE子句中会被逐行重复调用，每次调用都会独立生成新的随机值，而非仅在查询开始时执行一次返回固定结果。

• PostgreSQL中函数分为三个易失性等级：IMMUTABLE（输入相同则结果永远不变）、STABLE（同一查询扫描中输入相同则结果不变）、VOLATILE（结果可能随时变化，哪怕输入完全相同）。你创建函数时没有显式指定等级，PL/pgSQL默认会将函数标记为VOLATILE，且你的函数内部调用了本身就是VOLATILE等级的random()函数，这个标记完全符合函数的实际行为。
• 对于VOLATILE函数，PostgreSQL不会做“单次查询仅执行一次”的优化，只要表达式求值需要，就会重新执行函数。
• 你写的WHERE条件id = experiments.get_random_experiment_group('xxx')，执行逻辑是扫描experiments."group"表的每一行时，都重新调用一次随机分组函数，拿到一个新生成的分组ID，再判断当前行的id是否和这次的返回值相等。

你测试用的实验下共2条分组记录，因此查询时函数会被独立调用2次，每次调用有50%概率返回第一个分组ID、50%概率返回第二个分组ID，最终就会出现四种等概率的结果：

• 第一次调用返回分组A、第二次调用返回分组A：仅匹配第一行，返回1条记录
• 第一次调用返回分组B、第二次调用返回分组B：仅匹配第二行，返回1条记录
• 第一次调用返回分组A、第二次调用返回分组B：两行都匹配，返回2条记录
• 第一次调用返回分组B、第二次调用返回分组A：两行都不匹配，返回0条记录

这和你观察到的“有时1行、有时2行、有时无结果”的现象完全吻合。而你单独执行SELECT调用函数时，整个查询只触发1次函数调用，仅生成1个随机值，自然每次都只返回1个符合预期的分组ID。

不要直接把VOLATILE函数放在WHERE子句中和行字段做等值判断，要先将函数执行结果固化为查询期内的固定值，再做匹配，两种常用写法：

1. 用CTE提前计算函数结果，保证仅执行一次

WITH target_group AS (
    SELECT experiments.get_random_experiment_group('1a8cd547-8de6-4602-9103-3cea12f57365') AS group_id
)
SELECT g.id, g.experiment_id, g.proportion
FROM experiments."group" g, target_group tg
WHERE g.id = tg.group_id;

2. 用无关联标量子查询包裹函数，触发数据库仅执行一次求值

SELECT 
 id, 
 experiment_id,
 proportion 
FROM 
 experiments."group" 
WHERE 
 id = (SELECT experiments.get_random_experiment_group('1a8cd547-8de6-4602-9103-3cea12f57365'));

注意：不要为了省事把函数的易失性等级改成STABLE或IMMUTABLE，你的函数依赖随机值生成结果，修改易失性标记会导致数据库做出错误的执行计划优化，引发其他不可预期的问题。

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