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

User · Answer

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

User · Answer

See  http   www postgresql org docs 9 1 static arrays html  If your non-native driver still does not allow you to pass arrays  then you can    pass a string representation of an array  which your stored procedure can then parse into an array -- see string to array   CREATE FUNCTION my method TEXT  RETURNS VOID AS     DECLARE        ids INT    BEGIN        ids   string to array  1                  END    LANGUAGE plpgsql    then  SELECT my method  1    with  1    1 2 3 4  rely on Postgres itself to cast from a string to an array  CREATE FUNCTION my method INT    RETURNS VOID AS                END    LANGUAGE plpgsql    then  SELECT my method   1 2 3 4     choose not to use bind variables and issue an explicit command string with all parameters spelled out instead  make sure to validate or escape all parameters coming from outside to avoid SQL injection attacks    CREATE FUNCTION my method INT    RETURNS VOID AS                END    LANGUAGE plpgsql    then  SELECT my method ARRAY  1 2 3 4

User · Answer

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 lt int gt  pitem head id   get  set            NET CORE function DynamicParameters  parameter   new DynamicParameters                     parameter Add  quot  item head list quot  obj pitem head id                                    string sql    quot select   from admin usp itemdisplayid byitemhead select  item head list  quot                   response data   await  connection QueryAsync lt CampaignCreator gt  sql   parameter

User · Answer

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

[arrays] Postgres integer arrays as parameters?

Examples related to arrays

Examples related to postgresql

Examples related to argument-passing