Postgres 整数数组作为参数？

Question

据我了解，在 Postgres pure 中，您可以将整数数组传递到函数中，但 .NET 数据提供程序 Npgsql 不支持这一点。

我目前有一个 DbCommand，我在其中加载对存储过程的调用，添加参数并执行标量以获取用于填充对象的 Id。

现在需要将 n 个整数作为参数。 它们用于创建子记录，通过 id 将新创建的记录链接到整数参数。

理想情况下，我不想在 DbCommand 上为每个整数进行多次 ExecuteNonQuery 调用，因此我将构建一个 csv 字符串作为将在数据库端拆分的参数。

我通常生活在 LINQ 2 SQL 中，享受 Db 抽象，在这个项目中使用手动数据访问，这一切都变得有点脏，人们通常如何将这些类型的参数传递到 postgres 中？

Answer 1

请参阅：http://www.postgresql.org/docs/9.1/static/arrays .html

如果您的非本机驱动程序仍然不允许您传递数组，那么您可以：

• 传递数组的字符串表示形式（然后您的存储过程可以将其解析为数组 - 请参阅< a href="http://www.postgresql.org/docs/current/static/functions-array.html" rel="noreferrer">string_to_array)

  创建函数 my_method(TEXT) 返回 VOID AS $  
    宣布 
           id INT[]； 
    开始 
           ids = string_to_array($1,','); 
           ... 
    结束 $ 语言 plpgsql; 
    

  然后

  选择 my_method(:1) 
    

  with :1 = '1,2,3,4'

• 依靠Postgres本身从字符串转换为数组

  CREATE FUNCTION my_method(INT[]) 返回 VOID AS $  
           ... 
    结束 $ 语言 plpgsql; 
    

  然后

  SELECT my_method('{1,2,3,4}') 
    

• 选择不使用绑定变量并发出一个明确的命令字符串，其中所有参数都拼写出来（确保验证或转义所有参数）来自外部的参数以避免 SQL 注入攻击。）

  CREATE FUNCTION my_method(INT[]) 返回 VOID AS $  
           ... 
    结束 $ 语言 plpgsql; 
    

  然后

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

Answer 2

我意识到这是一个老问题，但我花了几个小时才找到一个好的解决方案，并认为我会传递我学到的东西此处 并省去其他人的麻烦。 例如，尝试

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

通过 where

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

command 是 NpgsqlCommand（在 Visual Studio 中使用 C# 和 Npgsql）将 @id_list 绑定到 int[] 参数。

Answer 3

完整的编码结构

postgresql函数

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$;

模型

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

.NET CORE函数

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);

Answer 4

您始终可以使用格式正确的字符串。 诀窍在于格式。

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

请注意，如果您的数组是字符串数组，那么您需要使用 array.Select(value => string.Format("\"{0}\", value)) 或等效方法。我使用这个PostgreSQL 中枚举类型数组的样式，因为

在我的例子中，我的枚举类型有一些值，例如 'value1'、'value2'、'value3'，并且我的 C# 枚举具有匹配的值。在我的例子中，最终的 SQL 查询最终看起来类似于 (E'{"value1","value2"} ')，这有效。