如何在查询WHERE子句中判断‘任意或限定’？修复PL/pgSQL函数报错

你遇到的missing FROM-clause entry for table "check_ids"报错，根源在于误用了数组的长度检查方式——PostgreSQL里不能像访问表属性那样用check_ids.count来获取数组长度，数组有专门的函数来处理这类判断。

正确的条件判断方式 #

要实现参数为NULL，或者数组为空，或者t.id存在于数组中，可以用以下两种推荐写法：

方式1：使用cardinality()函数（PostgreSQL 9.4+ 推荐）

这个函数直接返回数组的元素个数，空数组会返回0，非常直观：

check_ids IS NULL OR cardinality(check_ids) = 0 OR t.id = ANY(check_ids)

方式2：使用array_length()函数

如果你的PostgreSQL版本较低（低于9.4），可以用这个函数，它返回指定维度的数组长度，空数组会返回NULL：

check_ids IS NULL OR array_length(check_ids, 1) IS NULL OR t.id = ANY(check_ids)

修正后的完整函数 #

把错误的check_ids.count替换成上面的写法后，函数可以正常运行：

CREATE OR REPLACE FUNCTION func_example(
    IN check_ids INT [],
    OUT recs REFCURSOR,
    OUT po_result_code TEXT
) RETURNS RECORD LANGUAGE plpgsql AS $$
BEGIN
    OPEN recs FOR
        SELECT *
        FROM my_table t
        JOIN my_another_table tt on tt.tid = t.id
        WHERE t.enabled = TRUE
          AND tt.some = 1
          AND (check_ids IS NULL OR cardinality(check_ids) = 0 OR t.id = ANY(check_ids));
    
    po_result_code := '0'; -- 输出为TEXT类型，建议用字符串格式赋值
    RETURN;
END;
$$;

测试验证 #

用你提供的示例数据测试三种场景：

-- 场景1：传入NULL参数
SELECT * FROM func_example(NULL);

-- 场景2：传入空数组
SELECT * FROM func_example('{}'::INT[]);

-- 场景3：传入包含目标ID的数组
SELECT * FROM func_example('{1}'::INT[]);

三种情况都能正确返回符合条件的记录。

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