将多维数组作为参数传递给Postgresql函数 [英] Pass multidimensional array as parameter to Postgresql function

问题描述

我正在尝试使用PostgreSQL数据库维护Php应用程序.在某一时刻，调用了一个存储过程，比如说function_x，并且在function_x内部，调用了function_y. function_y传递了一个名为parameter_1的变量，而parameter_1的定义为:

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:

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

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

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:

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

2)Postgres数组变量忽略定义中的尺寸.我引用了此处的手册:

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

当前实现不强制声明的数量 尺寸.特定元素类型的数组都是 不论大小或数量，均被视为同一类型 方面.因此，声明数组的大小或维数 CREATE TABLE只是文档.它不会影响运行时行为.

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.

3)这是无效的语法:

3) This is invalid syntax:

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

需要单引号

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

实际上与

parameter_1 numeric[] := '{}';

与此紧密相关的答案中的更多详细信息，代码示例和链接:
返回行匹配plpgsql函数中输入数组的元素

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

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