代码之家 › 专栏 › 技术社区 › Ian

如何将复合类型传递给PL/pgsql函数?

npgsql plpgsql postgresql c#

Ian · 技术社区 · 7 年前

我正在尝试(未成功)将PostgreSql复合类型传递给PL/pgsql函数。下面列出了错误消息和示例代码。我还尝试了代码的几种不同变体(例如 this 未成功-每个版本都会生成不同的错误消息)。我是SQL新手,希望我犯了一个简单的错误。如果有人能查看下面的示例代码并解释我犯了什么错误,我将不胜感激。

错误消息

    System.InvalidCastException: When specifying NpgsqlDbType.Enum, SpecificType must be specified as well
       at Npgsql.TypeHandlerRegistry.get_Item(NpgsqlDbType npgsqlDbType, Type specificType)
       at Npgsql.NpgsqlParameter.ResolveHandler(TypeHandlerRegistry registry)
       at Npgsql.NpgsqlParameter.Bind(TypeHandlerRegistry registry)
       at Npgsql.NpgsqlCommand.ValidateParameters()
       at Npgsql.NpgsqlCommand.d__71.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
       at Npgsql.NpgsqlCommand.d__87.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
       at Npgsql.NpgsqlCommand.ExecuteScalar()
       at TestDatabase.TestCompositeType.Test() in F:\Visual Studio 2017\Projects\TestDatabase\TestDatabase\TestCompositeType.cs:line 42
       at TestDatabase.Program.Main(String[] args) in F:\Visual Studio 2017\Projects\TestDatabase\TestDatabase\Program.cs:line 17

系统信息

    Windows 10, 64 bit
    PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
    Npgsql, Version=3.2.6.0

PostgreSql代码

    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
            insert into MySchema.MyTable( MyType ) 
            values ( MyType2 )
            returning mt.ItemID into resultID;
        else
            update MySchema.MyTable as mt 
            set MyType = MyType2
            where mt.ItemID = ItemID2;
        end if;

        return resultID;
    end;
    $$ language plpgsql;

C#代码

    public void Test()
    {
        NpgsqlConnection.MapCompositeGlobally( "MySchema.MyType" );

        var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb" );
        if( null == connection )
            throw new NullReferenceException( "connection" );
        try
        {
            connection.Open();

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

            var par = new NpgsqlParameter( "ItemID2", NpgsqlDbType.Integer );
            par.Value = 1;
            cmd.Parameters.Add( par );

            par = new NpgsqlParameter( "MyType2", NpgsqlDbType.Composite );
            MyType myType = new MyType();
            myType.X = 1;
            myType.Y = 2;
            par.Value = myType;
            cmd.Parameters.Add( par );

            int id = Convert.ToInt32( cmd.ExecuteScalar() );
        }
        finally
        {
            connection.Close();
        }
    }

2 回复 | 直到 7 年前

Ian 7 年前

“sticky bit”提出的建议纠正了问题。我在下面为可能处理相同问题的任何其他人提供了更新的示例代码。所有字符串都使用“ToLower()”转换为小写,但这仅是在“NpgsqlConnection”中映射数据库类型所必需的。MapCompositeGlobal’。

    namespace TestDatabase
    {
        public class MyType
        {
            public float X;
            public float Y;
        };

        public class TestCompositeType
        {
            public void Test()
            {
                NpgsqlConnection.MapCompositeGlobally&LT;TestDatabase.MyType&GT;( "MySchema.MyType".ToLower() );

                var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb".ToLower() );
                if( null == connection )
                    throw new NullReferenceException( "connection" );
                try
                {
                    connection.Open();

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

                    var par = new NpgsqlParameter( "ItemID2".ToLower(), NpgsqlDbType.Integer );
                    par.Value = 1;
                    cmd.Parameters.Add( par );

                    par = new NpgsqlParameter( "MyType2".ToLower(), NpgsqlDbType.Composite );
                    MyType myType = new MyType();
                    myType.X = 1;
                    myType.Y = 2;
                    par.Value = myType;
                    par.SpecificType = typeof( MyType );
                    cmd.Parameters.Add( par );

                    int id = Convert.ToInt32( cmd.ExecuteScalar() );
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }

sticky bit 7 年前

通话前 connection.Open() 您必须通过调用 NpgsqlConnection.MapCompositeGlobally<>() 使用C#输入 <> (我想知道你的代码 <MyType> 部分,甚至编译?如果我这样做,我会出错。)

...
NpgsqlConnection.MapCompositeGlobally<MyType>( "MySchema.MyType" );

var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb" );
...

( MyType 必须具有不接受任何参数的公共构造函数 NpgsqlConnection。地图合成全局(&L)&燃气轮机;() 使用它。但你可能已经知道了。)

此外,必须设置属性 SpecificType 的 NpgsqlParameter 到您的C#类型。这就是你似乎真正错过的。(你在哪里做这件事并不重要(只要是在 ExecuteScalar() 当然,在创建参数之后),例如,在设置参数的 Value .)

...
myType.X = 1;
myType.Y = 2;
par.Value = myType;
par.SpecificType = typeof(MyType);
cmd.Parameters.Add( par );
...

都解释过了 "Accessing PostgreSQL Enums and Composites" 。

显然,在Npgsql的4.0版本中会变得更容易。我自己用Npgsql版本3.2.7 BTW测试了它。

编辑:

另一种解决方案是不指定 NpgsqlDbType 但是通过 MyType(我的类型) 对象。

...
MyType myType = new MyType();
myType.X = 1;
myType.Y = 2;
par = new NpgsqlParameter( "MyType2", myType );
cmd.Parameters.Add( par );
...

然后,右Postgres类型由C#类型到Prostgres类型的映射集旋转,映射集为 MapCompositeGlobally<>() 早些时候。显式设置 特定类型 则不需要。