Npgsql 6如何以存储过程形式执行带PSQL特有参数的PostgreSQL函数

问题场景 #

C#项目从Npgsql 2.2.7迁移至Npgsql 6.0.5（配套PostgreSQL 12.x版本）时出现函数调用适配问题。
Npgsql 2.2.7环境下可正常运行的代码如下：

using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "plainto_tsquery";
    cmd.Parameters.Add(new NpgsqlParameter(null, "german"));
    cmd.Parameters.Add(new NpgsqlParameter(null, "some test text"));
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
    // 运行结果为字符串: "'som' & 'test' & 'text'"
}

上述代码在Npgsql 6环境下执行ExecuteScalar时抛出异常：

Npgsql.PostgresException: '42883: Function plainto_tsquery(text, text) doesn't exists.'

查阅破坏性变更说明可知，新版本不再支持部分隐式类型转换，该异常符合预期，但尝试指定第一个参数为regconfig类型匹配函数签名时仍抛出异常，尝试代码如下：

cmd.Parameters.Add(null, NpgsqlDbType.Regconfig).Value = "german";

对应异常：

System.InvalidCastException: 'Can't write CLR type System.String with handler type UInt32Handler'

将CommandType改为Text、通过SELECT语句调用的方式可正常运行，但不希望采用该方案，可运行参考代码如下：

using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT plainto_tsquery($1::regconfig, $2)";
    cmd.Parameters.AddWithValue(NpgsqlDbType.Text, "german");
    cmd.Parameters.AddWithValue(NpgsqlDbType.Text, "some test text");
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
    // 运行结果为NpgsqlTypes.NpgsqlTsQueryAnd类型，ToString()输出为: "'som' & 'test' & 'text'"
}

核心疑问两点：

• 是否存在无需编写SELECT语句、直接以存储过程形式调用携带PostgreSQL特有类型参数的函数的方案？
• Npgsql 2.x版本是如何实现参数与命令文本的转换逻辑的？

解答 #

1. 存储过程形式调用的实现方案 #

Npgsql 6.0+版本中NpgsqlDbType.Regconfig对应的CLR类型是uint（对应PostgreSQL中regconfig类型的底层OID存储形式），直接传入字符串会触发类型转换错误。如果要保留CommandType.StoredProcedure的调用方式、不写SELECT语句，最简便的写法是不通过NpgsqlDbType枚举指定类型，直接给参数设置DataTypeName属性为regconfig，驱动会自动处理字符串到regconfig类型的转换，代码示例：

using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "plainto_tsquery";
    var configParam = new NpgsqlParameter
    {
        Value = "german",
        DataTypeName = "regconfig"
    };
    cmd.Parameters.Add(configParam);
    cmd.Parameters.Add(new NpgsqlParameter(null, "some test text"));
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
}

另外一种可行但更繁琐的方式是先查询对应文本搜索配置的OID，传入uint类型值匹配NpgsqlDbType.Regconfig的类型要求，实用性远低于第一种写法。
需要注意的是，Npgsql 6+版本中CommandType.StoredProcedure的调用逻辑已经和PostgreSQL的函数调用语义完全对齐，走扩展查询协议的参数绑定逻辑，不会自动做隐式类型转换，必须明确告知驱动参数对应的数据库实际类型。

2. Npgsql 2.x版本的参数转换逻辑 #

Npgsql 2.x版本的CommandType.StoredProcedure实现本质是字符串拼接生成SELECT调用语句，并非走PostgreSQL扩展查询协议的参数绑定：

• 驱动会把所有传入的参数值按照默认文本格式转义，直接拼接到函数调用的SQL文本中，实际执行的语句类似SELECT plainto_tsquery('german', 'some test text')
• 这种模式下PostgreSQL服务端会自动对字符串字面量做隐式类型转换，把'german'隐式转换为regconfig类型，因此不会报函数不存在的错误
• 该实现存在SQL注入风险，同时类型绑定不可靠，因此在3.0之后的版本逐步重构为严格的协议级参数绑定逻辑，移除了大量不可控的隐式转换逻辑，到6.0版本彻底废弃了旧的拼接逻辑，所有参数类型必须显式声明，不再依赖服务端隐式转换匹配函数签名。

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