I have the following type definition and function that consumes it:
create type my_type as
(
id bigint,
value character varying(100)
);
create or replace function my_function (
my_array my_type[])
returns table (id bigint)
language 'plpgsql'
as $BODY$
begin
return query
select id from my_table as t
where t.id in (
select ar.id
from unnest(my_array) as ar
);
end;
$BODY$;
The function above might be nonsensical functionally, but its simplified for minimum working example sake. I could call my_function in PostgreSQL this way:
select * from my_function(array[(1, 't')::my_type, (3, 'b')::my_type]);
Fiddle: https://dbfiddle.uk/SQAn2KZ4
I'm using .Net Core 8.0 and Npgsql 8.0.2 for the test code. Given the following POCO:
public class MyType
{
public int Id {get; set;}
public string Value {get; set;}
}
I have tried to call my_function this way:
var dsBuilder = new NpgsqlDataSourceBuilder("my-connection-string");
dsBuilder.MapComposite<MyType>("my_type");
using var ds = dsBuilder.Build();
using var conn = ds.OpenConnection();
var cmd = new NpgsqlCommand("select * from my_function(@arr)", conn);
cmd.Parameters.AddWithValue(
"arr",
new MyType[] {
new MyType(){ Id = 1, Value = "t" },
new MyType(){ Id = 3, Value = "b" },
}
)
The code above doesn't work, it gave an error saying Writing values of 'Program+MyType[]' is not supported for parameters having no NpgsqlDbType or DataTypeName. I also have tried to pass the parameter as IEnumerable and List to no avail. I do wonder how should I call my_function from C# using NpgsqlDataSource?
The documentations only shows example for a singular object. I have tried to look around for a way to pass an array of user type and only found example in python using tuples.
I have the final option to just push every MyType instance each in their own function calls, but that will be terribly inefficient and require significant changes to the PostgreSQL function. Alternatively, I could change the parameter to accept json - but is it the only way?
my_type[]is not a composite type - it's an array. You also don't need to cast @arr with::test_type[]- Npgsql infers the type automatically. Finally, there seems to be some type confusion: you define a MyType .NET type, but then use TestType below.NpgsqlConnection.GlobalTypeMapper.MapEnumand got rid ofINSERT INTOerrors. But now I'm getting errors on reading:Unable to cast object of type 'MyEnum[]' to type 'System.String[]'