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

问题描述

据我所知，Postgres的纯净，你可以通过一个整数数组的函数，但是，这不是在.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品尝的数据库抽象，在这个项目上与人工数据访问工作这一切都只是变得有点脏了，怎么办人们通常去传递这些类型的参数代入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?

推荐答案

请参阅：<一href=\"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:

• 再传递一个数组的presentation一个字符串（存储过程就可以解析到一个数组 - 见<一href=\"http://www.postgresql.org/docs/current/static/functions-array.html\"><$c$c>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)

有：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屋！