c# - 如何将复合类型传递给 PL/pgsql 函数？

Question

我正在尝试(未成功)将 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();        }    }   

Answer 1

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

    namespace TestDatabase    {        public class MyType        {            public float X;            public float Y;        };        public class TestCompositeType        {            public void Test()            {                NpgsqlConnection.MapCompositeGlobally<TestDatabase.MyType>( "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();                }            }        }    }