如何使用Npgsql 7与Dapper执行PostgreSQL 15自定义函数？

问题背景 #

使用PostgreSQL 15，项目已引入Dapper和Npgsql 7.0，需调用返回SETOF post_info的自定义函数get_all_posts，函数定义如下：

CREATE OR REPLACE FUNCTION get_all_posts(
    start_date date default null,
    end_date date default null,
    user_id integer default null,
    take_rows integer default 10,
    skip_rows integer default 0)
 RETURNS SETOF post_info
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY SELECT .....
END;
$function$
;

尝试两种调用方式均报错：

• 使用CommandType.StoredProcedure调用，报错：get_all_posts不是存储过程（Npgsql 7.0不再允许对函数使用CommandType.StoredProcedure）
• 使用SELECT * FROM get_all_posts($1, $2, $3, $4, $5)加匿名参数，报错：bind message supplies 0 parameters, but prepared statement "" requires

正确解决方案 #

方法1：命名参数SELECT调用（推荐） #

Dapper支持PostgreSQL的命名参数语法，直接匹配函数参数名，无需手动对应位置，且Npgsql会自动处理C#null到PostgreSQLNULL的映射，无需手动转DBNull.Value：

var sql = @"SELECT * FROM get_all_posts(
    start_date := @start_date,
    end_date := @end_date,
    user_id := @user_id,
    take_rows := @take_rows,
    skip_rows := @skip_rows
)";

using (var reader = await connection.ExecuteReaderAsync(sql, new
{
    start_date = postRequest.StartDate,
    end_date = postRequest.EndDate,
    user_id = postRequest.UserId,
    take_rows = take,
    skip_rows = skip
}, commandType: CommandType.Text))
{
    // 处理结果集逻辑
}

方法2：位置参数映射调用 #

若偏好位置参数$1、$2，需确保匿名对象属性顺序与参数位置严格对应，或用DynamicParameters明确绑定：

// 方式A：属性顺序匹配位置参数
var sql = "SELECT * FROM get_all_posts($1, $2, $3, $4, $5)";

using (var reader = await connection.ExecuteReaderAsync(sql, new
{
    postRequest.StartDate,
    postRequest.EndDate,
    postRequest.UserId,
    take,
    skip
}, commandType: CommandType.Text))
{
    // 处理结果集逻辑
}

// 方式B：DynamicParameters明确绑定位置
var parameters = new DynamicParameters();
parameters.Add("p1", postRequest.StartDate);
parameters.Add("p2", postRequest.EndDate);
parameters.Add("p3", postRequest.UserId);
parameters.Add("p4", take);
parameters.Add("p5", skip);

using (var reader = await connection.ExecuteReaderAsync(sql, parameters, commandType: CommandType.Text))
{
    // 处理结果集逻辑
}

方法3：Npgsql特定CALL语法（不推荐用于结果集返回） #

Npgsql支持CALL语法，但仅适合返回void或输出参数的函数，返回SETOF的场景仍推荐SELECT方式，示例如下：

using (var reader = await connection.ExecuteReaderAsync("CALL get_all_posts(@start_date, @end_date, @user_id, @take_rows, @skip_rows)", new
{
    start_date = postRequest.StartDate,
    end_date = postRequest.EndDate,
    user_id = postRequest.UserId,
    take_rows = take,
    skip_rows = skip
}, commandType: CommandType.Text))
{
    // 处理结果集逻辑
}

关键注意点 #

• Npgsql 7.0起，CommandType.StoredProcedure仅适用于PostgreSQL的存储过程（PROCEDURE），自定义函数必须通过CommandType.Text用SELECT调用。
• Dapper自动处理null值，无需手动转换为DBNull.Value。
• 命名参数比位置参数更可靠，可避免因参数顺序变更导致的错误。

内容的提问来源于stack exchange，提问作者juliano.net