如何向PL/pgsql函数传递PostgreSQL复合类型？

我来帮你排查这个问题——你遇到的System.InvalidCastException本质是处理复合类型参数时，没让Npgsql正确识别对应的.NET类型；另外你的PL/pgSQL函数还存在逻辑错误，咱们一步步修正：

1. 修正PL/pgSQL函数的逻辑错误 #

原函数里的if exists判断逻辑写反了：如果记录存在你执行的是insert（会触发主键冲突），不存在才执行update（根本找不到记录），完全不符合"存在则更新，不存在则插入"的预期。修正后的函数如下：

create schema if not exists MySchema;
create type MySchema.MyType as(
    X real,
    Y real
);
create table if not exists MySchema.MyTable(
    ItemID int primary key generated by default as identity,
    MyType MySchema.MyType
);
create or replace function MySchema.SetMyType(
    ItemID2 int,
    MyType2 MySchema.MyType
) returns int as $$
declare
    resultID int;
begin
    resultID := ItemID2;
    -- 修正逻辑：存在则更新，不存在则插入
    if exists( select 1 from MySchema.MyTable as mt where mt.ItemID = ItemID2 ) then
        update MySchema.MyTable as mt set MyType = MyType2 where mt.ItemID = ItemID2;
    else
        -- 显式指定ItemID，避免使用自动生成的主键值
        insert into MySchema.MyTable(ItemID, MyType) values (ItemID2, MyType2) returning ItemID into resultID;
    end if;
    return resultID;
end;
$$ language plpgsql;

2. 修正C#代码的复合类型传递问题 #

你需要让.NET类和PostgreSQL复合类型正确绑定，同时修正参数的设置方式：

第一步：定义匹配的.NET复合类型 #

先创建一个和PostgreSQLMySchema.MyType字段完全对应的C#类，并用[PgName]属性指定映射关系：

using NpgsqlTypes;

[PgName("MySchema.MyType")]
public class MyType
{
    public float X { get; set; }
    public float Y { get; set; }
}

第二步：修正参数传递逻辑 #

映射复合类型时要指定对应的.NET类型，参数设置可以让Npgsql自动推断类型，避免手动指定NpgsqlDbType.Composite时遗漏SpecificType：

public void Test()
{
    // 全局映射复合类型，关联.NET类和PostgreSQL类型
    NpgsqlConnection.MapCompositeGlobally<MyType>("MySchema.MyType");

    // 使用using语句自动管理资源，替代手动try-finally关闭连接
    using var connection = new NpgsqlConnection("Host=localhost;Username=postgres;Password=123456;database=testdb");
    connection.Open();

    using var cmd = new NpgsqlCommand("MySchema.SetMyType", connection);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    // 添加整数参数
    cmd.Parameters.Add(new NpgsqlParameter("ItemID2", NpgsqlDbType.Integer) { Value = 1 });

    // 添加复合类型参数：让Npgsql自动推断类型，无需手动指定NpgsqlDbType.Composite
    var myType = new MyType { X = 1, Y = 2 };
    cmd.Parameters.Add(new NpgsqlParameter("MyType2", myType));

    int id = Convert.ToInt32(cmd.ExecuteScalar());
}

关键问题总结 #

• 你之前的InvalidCastException是因为使用NpgsqlDbType.Composite时没有指定SpecificType，Npgsql无法找到对应的类型处理程序；
• 确保.NET类的属性名称和PostgreSQL复合类型的字段名称完全匹配（大小写不敏感，但保持一致更稳妥）；
• 使用using语句可以自动释放数据库连接和命令资源，避免手动管理的遗漏。

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