PostgreSQL的plpgsql函数如何在IN子句中使用声明的变量列表

错误原因 #

你原来的函数入参是单个text类型，只能存储单个字符串，就算你传入多值格式的文本，PostgreSQL也不会自动把它解析为多个值匹配IN子句，自然无法得到预期结果。

提供两种常用的实现方式，按需选择即可： #

方案1：使用数组类型作为入参 #

修改函数入参为文本数组类型，配合ANY运算符匹配多值，代码如下：

CREATE or REPLACE FUNCTION name_list(name_of_interest text[])
RETURNS table (name text,
               sex text,
               age int,
               state text)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN query
SELECT name, sex, age, state FROM employee
INNER JOIN address_book ON employee.id = address_book.id
WHERE name = ANY(name_of_interest);
END;
$$;

调用方式：

SELECT * FROM name_list(ARRAY['Harry Potter', 'Brad Pitt', 'Stephen Curry']);

同时也兼容单姓名查询：SELECT * FROM name_list(ARRAY['Harry Potter']); #

方案2：使用可变参数（VARIADIC）简化调用 #

如果觉得每次传数组需要加ARRAY前缀太麻烦，可以用可变参数定义入参，调用时可以直接传入多个字符串参数，使用体验和原来的单参数调用一致：

CREATE or REPLACE FUNCTION name_list(VARIADIC name_of_interest text[])
RETURNS table (name text,
               sex text,
               age int,
               state text)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN query
SELECT name, sex, age, state FROM employee
INNER JOIN address_book ON employee.id = address_book.id
WHERE name = ANY(name_of_interest);
END;
$$;

调用方式更简洁：

-- 多姓名查询
SELECT * FROM name_list('Harry Potter', 'Brad Pitt', 'Stephen Curry');
-- 单姓名查询和原有写法完全兼容
SELECT * FROM name_list('Harry Potter');

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