title: How to pass composite types to PL/pgsql function?
body: <p>I am trying (unsuccessfully) to pass a PostgreSql composite type to a PL/pgsql function.  The error message and sample code are listed below.  I have also tried several different variations on the code (such as <a href="https://stackoverflow.com/questions/42337076/how-to-return-custom-table-types-from-npgsql-and-stored-procedures?rq=1%22this%22">this</a> without success - each version generates a different error message).  I am new to SQL and expect I am making a simple mistake.  I would appreciate if someone can review the sample code below and explain what the mistake is that I have made. </p>
  
  <p><strong>Error message</strong></p>
  
  <pre>
  
      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
  
  </pre>
  
  <p><strong>System information</strong></p>
  
  <pre>
  
      Windows 10, 64 bit
      PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
      Npgsql, Version=3.2.6.0
  
  </pre>
  
  <p><b>PostgreSql code</b></p>
  
  <pre>
  
      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;
  
  </pre>
  
  <p><strong>C# code</strong></p>
  
  <pre>
  
      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();
          }
      }   
  
  </pre>