1

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?

4
  • 2
    Try removing the 2nd MapComposite line: 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. Commented Mar 22, 2024 at 9:43
  • @ShayRojansky yeah, some of it was typo. I tried without the second mapping previously and still not working, forgot to remove it from the question. guess i'm too tired with fumbling around on this all day. Commented Mar 22, 2024 at 12:11
  • Any luck with this? I tried using NpgsqlConnection.GlobalTypeMapper.MapEnum and got rid of INSERT INTO errors. But now I'm getting errors on reading: Unable to cast object of type 'MyEnum[]' to type 'System.String[]' Commented May 22, 2024 at 15:04
  • 1
    @NikitaKalimov i gave up and use json data type instead.. Commented May 22, 2024 at 15:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.