PostgreSQL RPC如何允许ANY/IN所用必填数组参数为null或空

原有写法无法返回正确结果，核心原因有两点：

• 仅判断了参数为NULL的场景，未覆盖传入空数组的情况：当参数是空数组时，$2 is null返回false，逻辑会走到= any(空数组)分支，该表达式永远返回false，导致无匹配结果
• 未做严格类型对齐：ANY操作符要求两侧值类型完全一致，若传入的数组参数类型和左侧JSON字段转出的BIGINT类型不匹配，会出现隐式转换失败、匹配失效的问题

可直接使用的兼容写法 #

判断逻辑同时覆盖参数为NULL、参数为空数组两种「不限制当前筛选维度」的场景，代码如下：

SELECT *
FROM dev.get_all_articles
WHERE 
  -- 商品名称模糊匹配，若需支持名称参数为空可放开NULL判断
  ($1 IS NULL OR get_all_articles.lib_article ILIKE '%' || $1 || '%')
  AND (
    $2 IS NULL
    OR cardinality($2) = 0
    OR CAST(get_all_articles.catg_et_type->>'id_catgarticle' AS BIGINT) = ANY ($2::bigint[])
  );

关键逻辑说明 #

• cardinality($2) = 0用于判断数组是否为空，该函数返回数组所有维度的总元素数，空数组返回0，比array_length()适配性更强，不会因多维数组出现判断偏差
• $2::bigint[]做强制类型转换，保证传入数组的元素类型和左侧转出的BIGINT类型完全对齐，避免隐式类型转换导致的匹配错误

多筛选维度扩展方式 #

存在颜色、品牌等多个同类型数组筛选参数时，直接按相同模式拼接条件即可，无需手动枚举所有参数组合：

SELECT *
FROM dev.get_all_articles
WHERE 
  ($1 IS NULL OR lib_article ILIKE '%' || $1 || '%')
  AND ($2 IS NULL OR cardinality($2)=0 OR CAST(catg_et_type->>'id_catgarticle' AS BIGINT) = ANY($2::bigint[]))
  AND ($3 IS NULL OR cardinality($3)=0 OR CAST(color_et_type->>'id_colorarticle' AS BIGINT) = ANY($3::bigint[]))
  AND ($4 IS NULL OR cardinality($4)=0 OR CAST(brand_et_type->>'id_brandarticle' AS BIGINT) = ANY($4::bigint[]));

注意：如果RPC层对参数做了类型校验，确保传入的数组参数和数据库内转换后的类型一致，可以省略::bigint[]的强制转换步骤。

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