数据库函数中text[]数组参数的IN查询语法错误解决

我正在修改一个数据库函数，需要添加查询逻辑，让商品的分类ID匹配传入数组中的任意值。

示例输入（JavaScript）：

let categories = ['10272111', '165796011', '3760911', '9223372036854776000','3760901','7141123011']

数据库函数中的部分SQL代码：

(case when brand is null then '' else 'AND ra.brand ILIKE ''%%''||brand||''%%''' end),
(case when category is null then '' else 'AND ra.root_category IN ('||category||')' end),
(case when asin is null then '' else 'AND ra.asin ILIKE ''%%''||asin||''%%''' end),

函数顶部的参数定义：

category text[] DEFAULT NULL::text[],

当前遇到的错误：

malformed array literal: "AND ra.root_category IN ("

你当前的问题是直接将数组类型的category参数拼入SQL字符串，PostgreSQL数组的默认字符串格式为{元素1,元素2}，和IN语法要求的列表格式不匹配，同时未处理数组为空的场景，导致语法报错。以下是两种正确实现方式：

方法一：使用ANY操作符（推荐） #

PostgreSQL原生支持用ANY操作符判断字段是否匹配数组中的任意元素，写法简洁且安全：

(case 
    when category is null or cardinality(category) = 0 then '' 
    else 'AND ra.root_category = ANY('||quote_literal(category)||')' 
end),

• cardinality()用于获取数组长度，处理空数组场景；
• quote_literal()自动处理特殊字符，避免SQL注入风险。

方法二：手动拼接IN列表 #

如果必须使用IN语法，需要将数组转换为逗号分隔的带单引号的元素列表：

(case 
    when category is null or cardinality(category) = 0 then '' 
    else 'AND ra.root_category IN ('||array_to_string(array_agg(quote_literal(elem)), ',')||')' 
end),

• array_agg(quote_literal(elem))给每个数组元素添加单引号；
• array_to_string将处理后的元素拼接为符合IN要求的字符串。

关键注意点 #

• 禁止直接拼接数组参数，其默认格式不兼容IN语法；
• 必须处理数组为空或null的情况，否则会生成IN ()这类无效SQL；
• 始终用quote_literal或quote_nullable处理字符串参数，规避SQL注入风险。

内容的提问来源于stack exchange，提问作者Coşkun