Postgres integer arrays as parameters?

Question

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?

Answer 1

Have you looked at the section "Working with arrays" at http://npgsql.projects.postgresql.org/docs/manual/UserManual.html ?

If your non-native driver still does not allow you to pass arrays, then you can always forfeit the use of prepared statements with bind variables, and issue textual commands instead (Postgres does not natively support prepared statements with bind variables anyway, a textual command is generated by the intermediate driver every time anyway, enable statement logging in postgresql.conf to confirm.)

"SELECT my_method(ARRAY [1,2,3,4])"

Cheers, V.