.NET 7 Web API调用PostgreSQL函数报42601错误的解决咨询

问题原因 #

1. PostgreSQL函数与存储过程的本质区别：Dapper的CommandType.StoredProcedure仅适配PostgreSQL中用CREATE PROCEDURE创建的存储过程，而你调用的是CREATE FUNCTION定义的函数，两者调用语法完全不同，指定该类型必然触发报错。
2. Dapper默认生成语法不符合PostgreSQL函数规范：直接传递函数名并指定存储过程类型时，Dapper生成的SQL语句不符合PostgreSQL函数的调用格式，导致语法错误（42601）。

解决办法 #

1. 使用文本命令类型，手动构造函数调用SQL #

将CommandType设为Text，按照PostgreSQL函数的标准调用语法编写SQL，示例代码如下：

// 构造符合PostgreSQL要求的函数调用SQL
var sql = "SELECT * FROM getallvendormasternew(@Param1, @Param2)";
// 准备参数
var parameters = new DynamicParameters();
parameters.Add("@Param1", paramValue1);
parameters.Add("@Param2", paramValue2);
// 执行查询
var vendors = await connection.QueryAsync<VendorMasterModel>(sql, parameters, commandType: CommandType.Text);

注意：参数名需与PostgreSQL函数定义中的参数名或位置对应（PostgreSQL支持按名称或位置传参）。

2. 确认函数的架构与权限 #

• 在PostgreSQL终端执行\df getallvendormasternew，查看函数的所属架构（如public）、参数列表、返回类型，确保调用时的架构名正确（比如public.getallvendormasternew是否匹配实际架构）。
• 确认数据库连接用户拥有该函数的执行权限，若无权限可执行GRANT EXECUTE ON FUNCTION getallvendormasternew(参数类型列表) TO 用户名;完成授权。

3. 验证函数的返回类型 #

确保函数是返回表类型的，因为QueryAsync用于查询数据集。若函数返回单个值或非表类型，需调整调用方式（比如用ExecuteScalarAsync）。典型的表返回函数定义示例：

CREATE OR REPLACE FUNCTION getallvendormasternew(filter_param VARCHAR)
RETURNS TABLE (vendor_id INT, vendor_name VARCHAR, contact_email VARCHAR) AS $$
BEGIN
    RETURN QUERY SELECT vm.vendor_id, vm.vendor_name, vm.contact_email
                 FROM vendor_master vm
                 WHERE vm.vendor_name LIKE '%' || filter_param || '%';
END;
$$ LANGUAGE plpgsql;

4. 区分存储过程与函数的调用方式 #

若你确实需要使用存储过程，需先将函数改为CREATE PROCEDURE定义的存储过程，调用时使用CALL语法并指定CommandType.StoredProcedure；但查询数据场景下，PostgreSQL返回表类型的函数是更合适的选择。

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