[arrays] Postgres integer arrays as parameters?

I understand that in Postgres pure, you can pass an integer array into a function but that this isn't supported in the .NET data provider Npgsql.

I currently have a DbCommand into which I load a call to a stored proc, add in a parameter and execute scalar to get back an Id to populate an object with.

This now needs to take n integers as arguments. These are used to create child records linking the newly created record by it's id to the integer arguments.

Ideally I'd rather not have to make multiple ExecuteNonQuery calls on my DbCommand for each of the integers, so I'm about to build a csv string as a parameter that will be split on the database side.

I normally live in LINQ 2 SQL savouring the Db abstraction, working on this project with manual data access it's all just getting a bit dirty, how do people usually go about passing these kinds of parameters into postgres?

Full Coding Structure

postgresql function

CREATE OR REPLACE FUNCTION admin.usp_itemdisplayid_byitemhead_select(
    item_head_list int[])
    RETURNS TABLE(item_display_id integer) 
    LANGUAGE 'sql'

    COST 100
    VOLATILE 
    ROWS 1000
    
AS $BODY$ 
        SELECT vii.item_display_id from admin.view_item_information as vii
where vii.item_head_id = ANY(item_head_list);
    $BODY$;

Model

public class CampaignCreator
    {
        public int item_display_id { get; set; }
        public List<int> pitem_head_id { get; set; }
    }

.NET CORE function

DynamicParameters _parameter = new DynamicParameters();
                _parameter.Add("@item_head_list",obj.pitem_head_id);
                
                string sql = "select * from admin.usp_itemdisplayid_byitemhead_select(@item_head_list)";
                response.data = await _connection.QueryAsync<CampaignCreator>(sql, _parameter);

You can always use a properly formatted string. The trick is the formatting.

command.Parameters.Add("@array_parameter", string.Format("{{{0}}}", string.Join(",", array));

Note that if your array is an array of strings, then you'll need to use array.Select(value => string.Format("\"{0}\", value)) or the equivalent. I use this style for an array of an enumerated type in PostgreSQL, because there's no automatic conversion from the array.

In my case, my enumerated type has some values like 'value1', 'value2', 'value3', and my C# enumeration has matching values. In my case, the final SQL query ends up looking something like (E'{"value1","value2"}'), and this works.

I realize this is an old question, but it took me several hours to find a good solution and thought I'd pass on what I learned here and save someone else the trouble. Try, for example,

SELECT * FROM some_table WHERE id_column = ANY(@id_list)

where @id_list is bound to an int[] parameter by way of

command.Parameters.Add("@id_list", NpgsqlDbType.Array | NpgsqlDbType.Integer).Value = my_id_list;

where command is a NpgsqlCommand (using C# and Npgsql in Visual Studio).