将多维数组作为参数传递给Postgresql函数

I'm trying to maintain a Php application with a PostgreSQL database. At one point, a stored procedure is called, lets say function_x and inside function_x, function_y is called; function_y is passed a variable named parameter_1, and the definition of parameter_1 is:

我正在尝试使用PostgreSQL数据库维护一个Php应用程序。在某一点上,调用一个存储过程,比如function_x和function_x,调用function_y; function_y传递一个名为parameter_1的变量,parameter_1的定义是:

parameter_1 numeric[][3] := {};

I'm trying to do a select function_y directly on the command line (or pgadmin) but I'm having problems passing an empty array into the function. according to the docs you have to use variadic but so I tried:

我正在尝试直接在命令行(或pgadmin)上执行select function_y但是我在将空数组传递给函数时遇到了问题。根据你必须使用variadic的文档,但我试过:

select function_y(581, 'CPN-00000000001-0000', 'TPN-00000000001-0001', 100, 2013, variadic arr := array[]);

But I got this error:

但是我收到了这个错误:

ERROR:  cannot determine type of empty array

I tried different approaches but nothing works. How can I pass a multidimensional array as a parameter at a query?

我尝试了不同的方法但没有任何作用如何在查询中将多维数组作为参数传递?

1 个解决方案

#1

1) You can, but you do not have to use VARIADIC parameters for array variables. You'd have to use it in the declaration of the function, not in the call, though.

1)您可以,但您不必将VARIADIC参数用于数组变量。但是,您必须在函数声明中使用它,而不是在调用中使用它。

2) Postgres array variables ignore dimensions in the definition. I quote the manual here:

2)Postgres数组变量忽略定义中的维度。我在这里引用手册:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

当前实现也不强制声明的维数。无论尺寸大小或数量如何,特定元素类型的数组都被认为是相同类型的。因此,在CREATE TABLE中声明数组大小或维数是简单的文档;它不会影响运行时行为。

3) This is invalid syntax:

3)这是无效的语法:


  
  
  
    parameter_1 numeric[][3] := {};

Single quotes are required:

单引号是必需的:

parameter_1 numeric[][3] := '{}';

Which is effectively the same as

这实际上是一样的

parameter_1 numeric[] := '{}';

More details, code examples and links in this closely related answer:
Return rows matching elements of input array in plpgsql function

这个密切相关的答案中的更多细节,代码示例和链接:返回与plpgsql函数中的输入数组元素匹配的行

#1