postgresql Postgres 整数数组作为参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/570393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres integer arrays as parameters?
提问by Tristan Warner-Smith
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.
我知道在 Postgres pure 中，您可以将一个整数数组传递给一个函数，但 .NET 数据提供程序 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.
我目前有一个 DbCommand，我在其中加载对存储过程的调用，添加参数并执行标量以获取 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.
现在需要将 n 个整数作为参数。这些用于创建子记录，通过它的 id 将新创建的记录链接到整数参数。
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.
理想情况下，我不想在我的 DbCommand 上为每个整数进行多次 ExecuteNonQuery 调用，因此我将构建一个 csv 字符串作为将在数据库端拆分的参数。
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?
我通常住在 LINQ 2 SQL 中，品味 Db 抽象，在这个项目上使用手动数据访问，这一切都变得有点脏，人们通常如何将这些类型的参数传递给 postgres？
回答by vladr
See: http://www.postgresql.org/docs/9.1/static/arrays.html
请参阅：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(,','); ... 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])
传递数组的字符串表示（然后您的存储过程可以将其解析为数组 - 请参阅
string_to_array）CREATE FUNCTION my_method(TEXT) RETURNS VOID AS $$ DECLARE ids INT[]; BEGIN ids = string_to_array(,','); ... END $$ LANGUAGE plpgsql;然后
SELECT my_method(:1):1 =
'1,2,3,4'依靠 Postgres 本身将字符串转换为数组
CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ ... END $$ LANGUAGE plpgsql;然后
SELECT my_method('{1,2,3,4}')选择不使用绑定变量并发出一个明确的命令字符串，所有参数都拼写出来（确保验证或转义来自外部的所有参数以避免 SQL 注入攻击。）
CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ ... END $$ LANGUAGE plpgsql;然后
SELECT my_method(ARRAY [1,2,3,4])
回答by brichins
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 hereand 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
其中@id_list 通过以下方式绑定到一个 int[] 参数
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).
其中 command 是 NpgsqlCommand（在 Visual Studio 中使用 C# 和 Npgsql）。
回答by user2738265
You can alwaysuse a properly formatted string. The trick is the formatting.
您始终可以使用格式正确的字符串。诀窍是格式化。
command.Parameters.Add("@array_parameter", string.Format("{{{0}}}", string.Join(",", array));
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.
请注意，如果您的数组是一个字符串数组，那么您需要使用 array.Select(value => string.Format("\"{0}\", value)) 或等价物。我使用这种样式PostgreSQL 中枚举类型的数组，因为没有从数组自动转换。
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.
就我而言，我的枚举类型有一些值，如“value1”、“value2”、“value3”，而我的 C# 枚举具有匹配的值。在我的情况下，最终的 SQL 查询最终看起来像 (E'{"value1","value2"}')，这是有效的。
