Postgres 整数数组作为参数? [英] Postgres integer arrays as parameters?

问题描述

我知道在 Postgres pure 中，您可以将整数数组传递给函数，但 .NET 数据提供程序 Npgsql 不支持这种做法.

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.

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

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.

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

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.

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

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.

我通常在 LINQ 2 SQL 中享受 Db 抽象，使用手动数据访问在这个项目上工作它只是变得有点脏，人们通常如何将这些类型的参数传递给 postgres?

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?

推荐答案

参见: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:

• 传递一个数组的字符串表示(然后您的存储过程可以将其解析为一个数组——请参阅string_to_array)

CREATE FUNCTION my_method(TEXT) RETURNS VOID AS $$ 
DECLARE
       ids INT[];
BEGIN
       ids = string_to_array($1,',');
       ...
END $$ LANGUAGE plpgsql;

然后

SELECT my_method(:1)

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

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

rely on Postgres itself to cast from a string to an array

CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ 
       ...
END $$ LANGUAGE plpgsql;

然后

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

选择不使用绑定变量并发出明确的命令字符串，并将所有参数拼写出来(确保验证或转义来自外部的所有参数，以避免 SQL 注入攻击.)

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;

然后

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

这篇关于Postgres 整数数组作为参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！