求助：PostgreSQL创建带可选参数的存储过程/函数示例

一、函数实现（推荐，直接返回结果集） #

如果需要直接返回查询结果，用函数更便捷，以下是完整实现：

CREATE OR REPLACE FUNCTION fn_test_func(
    param1 varchar,
    param2 varchar,
    param3 varchar,
    param4 timestamp,
    param5 timestamp,
    param6 int,
    param7 varchar
) RETURNS SETOF myTable AS $$
DECLARE
    sql_query text;
BEGIN
    -- 初始化基础查询语句
    sql_query := 'SELECT * FROM myTable WHERE 1=1';

    -- 根据参数非空情况拼接条件，使用format避免SQL注入
    IF param1 IS NOT NULL THEN
        sql_query := format('%s AND column1 = $1', sql_query);
    END IF;

    IF param2 IS NOT NULL THEN
        sql_query := format('%s AND column2 = $2', sql_query);
    END IF;

    IF param3 IS NOT NULL THEN
        sql_query := format('%s AND column3 = $3', sql_query);
    END IF;

    IF param4 IS NOT NULL THEN
        sql_query := format('%s AND column4 = $4', sql_query);
    END IF;

    IF param5 IS NOT NULL THEN
        sql_query := format('%s AND column5 = $5', sql_query);
    END IF;

    IF param6 IS NOT NULL THEN
        sql_query := format('%s AND column6 = $6', sql_query);
    END IF;

    IF param7 IS NOT NULL THEN
        sql_query := format('%s AND column7 = $7', sql_query);
    END IF;

    -- 执行动态SQL并返回结果，USING传递参数防止注入
    RETURN QUERY EXECUTE sql_query
    USING param1, param2, param3, param4, param5, param6, param7;
END;
$$ LANGUAGE plpgsql;

二、存储过程实现（适合包含事务逻辑的场景） #

如果需要包含事务操作，用存储过程更合适，以下是返回结果集的存储过程示例：

CREATE OR REPLACE PROCEDURE sp_test_sproc(
    param1 varchar,
    param2 varchar,
    param3 varchar,
    param4 timestamp,
    param5 timestamp,
    param6 int,
    param7 varchar,
    OUT result refcursor
) AS $$
DECLARE
    sql_query text;
BEGIN
    -- 初始化游标并返回结果
    OPEN result FOR
        SELECT * FROM myTable WHERE 1=1
        AND (param1 IS NULL OR column1 = param1)
        AND (param2 IS NULL OR column2 = param2)
        AND (param3 IS NULL OR column3 = param3)
        AND (param4 IS NULL OR column4 = param4)
        AND (param5 IS NULL OR column5 = param5)
        AND (param6 IS NULL OR column6 = param6)
        AND (param7 IS NULL OR column7 = param7);
END;
$$ LANGUAGE plpgsql;

关键说明 #

• 避免SQL注入：函数使用format()和USING子句传递参数，存储过程直接用参数IS NULL OR 字段=参数的条件判断，都能避免直接拼接参数带来的注入风险，比原始字符串拼接更安全。
• 字段名替换：请把示例中的column1、column2等替换为你实际表myTable中的对应字段名。
• 调用方式：
  • 函数调用：SELECT * FROM fn_test_func('val1', NULL, 'val3', NULL, NULL, 6, 'val7');
  • 存储过程调用：

    BEGIN;
    CALL sp_test_sproc('val1', NULL, 'val3', NULL, NULL, 6, 'val7', 'result_cursor');
    FETCH ALL FROM result_cursor;
    CLOSE result_cursor;
    COMMIT;
    

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