PostgreSQL两种数组合并函数的行为差异及原因咨询

我创建了两个功能相似的数组合并函数：SQL版本的mergeArrays和PL/pgSQL版本的mergeArrays_plpgsql，原本预期二者行为一致，但测试发现：当其中一个输入数组为NULL时，SQL版函数无法完成合并，而PL/pgSQL版函数能正常执行合并操作。

测试环境与数据 #

表结构 #

-- 未定义主键/外键
CREATE TABLE userposix (
  username  varchar,
  groupname varchar,
  uoptions  varchar[]
);

CREATE TABLE groupposix (
  groupname varchar,
  goptions  varchar[]
);

插入测试数据 #

INSERT INTO groupposix VALUES ('g1','{o1,o3}');
INSERT INTO groupposix VALUES ('g2');

INSERT INTO userposix VALUES ('u1','g1','{o1,o2}');
INSERT INTO userposix VALUES ('u2','g1');
INSERT INTO userposix VALUES ('u3','g2','{o4,o2}');

基础数据查询结果 #

username | groupname | uoptions | goptions 
----------+-----------+----------+----------
 u2       | g1        |          | {o1,o3}
 u1       | g1        | {o1,o2}  | {o1,o3}
 u3       | g2        | {o4,o2}  | 
(3 rows)

函数调用测试 #

查询语句 #

SELECT
    username,
    uoptions || goptions AS "just_concat",
    mergeArrays(uoptions,goptions),
    mergeArrays_plpgsql(uoptions,goptions)
  FROM userposix u
    LEFT JOIN groupposix g USING (groupname);

查询结果 #

username |  just_concat  | mergearrays | mergearrays_plpgsql 
----------+---------------+-------------+--------------------- 
 u2       | {o1,o3}       |             | {o1,o3}
 u1       | {o1,o2,o1,o3} | {o1,o2,o3}  | {o1,o2,o3}
 u3       | {o4,o2}       |             | {o2,o4}
(3 rows)

同时，直接SQL子查询的结果与PL/pgSQL版函数一致，符合预期。请问为何SQL版与PL/pgSQL版函数会出现这种行为差异？

核心差异在于SQL函数和PL/pgSQL函数对NULL输入的处理逻辑不同：

• SQL函数的NULL传播特性
  SQL函数本质是纯表达式组合，PostgreSQL中任何包含NULL值的表达式，结果都会直接返回NULL（除非用COALESCE这类函数显式处理）。如果你的mergeArrays函数未对输入的NULL数组做转换，比如直接使用ARRAY(SELECT DISTINCT unnest(a || b))这类逻辑，当a或b为NULL时，a || b的结果就是NULL，后续的unnest和数组重构也会返回NULL，最终函数输出NULL。

• PL/pgSQL函数的灵活NULL处理
  PL/pgSQL函数在执行时对NULL输入的处理更灵活：要么你在函数代码里显式用COALESCE把NULL转为空数组（比如COALESCE(a, '{}'::varchar[])），要么PL/pgSQL会隐式将NULL数组当作空数组处理，这样合并逻辑就能正常执行——即使其中一个输入是NULL，也会和另一个有效数组合并，最终返回去重后的结果。

简单来说，SQL函数默认严格遵循NULL传播规则，而PL/pgSQL函数因为有显式或隐式的NULL兼容处理，所以能应对其中一个输入为NULL的场景。

内容的提问来源于stack exchange，提问作者Javi M.