PostgreSQL：如何基于自定义元组数组构建WHERE子句

方法1：直接使用数组与= ANY操作符（推荐） #

无需编写函数，直接利用PostgreSQL的行构造器和数组匹配功能即可实现需求，既避免SQL注入风险，性能表现也更优：

SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) = ANY(ARRAY[('file1', idx1), ('file2', idx2)]::file_idx_pair[]);

其中file_idx_pair是你需要提前定义的自定义类型：

CREATE TYPE file_idx_pair AS (file text, idx smallint);

使用时只需传入构造好的数组参数即可，不管是在应用代码中传递数组，还是直接在SQL里构造数组都适用。

方法2：生成WHERE子句的函数实现 #

如果确实需要通过函数生成WHERE子句字符串，可以用以下安全的PL/pgSQL函数，借助format和string_agg拼接条件，同时规避SQL注入：

1. 定义自定义类型 #

CREATE TYPE file_idx_pair AS (file text, idx smallint);

2. 创建生成WHERE子句的函数 #

CREATE OR REPLACE FUNCTION generate_file_idx_where(p_project_id int, p_pairs file_idx_pair[])
RETURNS text AS $$
BEGIN
  RETURN format('project = %L AND (%s)',
                p_project_id,
                string_agg(format('file = %L AND fieldidx = %s', pair.file, pair.idx), ' OR ')
               )
  FROM unnest(p_pairs) AS pair;
END;
$$ LANGUAGE plpgsql;

3. 函数调用示例 #

-- 生成目标WHERE子句
SELECT generate_file_idx_where(123, ARRAY[('file1', 1), ('file2', 2)]::file_idx_pair[]);

返回结果为：
project = '123' AND (file = 'file1' AND fieldidx = 1 OR file = 'file2' AND fieldidx = 2)

你可以将该结果直接拼接进完整的SQL查询中使用。

内容的提问来源于stack exchange，提问作者Edmund's Echo