函数中的初始数组聚合多维数组 [英] Initial array in function to aggregate multi-dimensional array

问题描述

我有一个包含整数数组的表格.

我想创建一个聚合函数，它将返回一个包含所有行的二维数组.然后将其传递给 plr 以对其进行一些数学运算.

我有:

创建或替换函数数组追加(左 int[][]，右 int[])返回 int[] AS$BODY$选择 $1 ||2 美元；$BODY$语言 SQL;

和:

CREATE AGGREGATE array_sum2 (int[]) (SFUNC = 数组追加，STYPE = int[][],INITCOND = '{}');

但是返回类型是int[]，而不是int[][]?

如何用一个空的二维整数数组初始化聚合?

解决方案

Postgres 9.5 或更新版本

... 附带聚合函数 array_agg() 的附加变体.手册:

<块引用>

输入数组连接成一个更高维度的数组(输入必须都具有相同的维度，并且不能为空或为空)

所以与下面的自定义聚合函数array_agg_mult() 不完全相同.但是，如果可以，请使用它.它更快.

相关:

• 如何对二维整数数组进行排序在 PostgreSQL 中?

Postgres 9.4 或更早版本

任何数组类型的聚合函数

使用 多态类型 anyarray 它适用于所有类型的数组(包括 integer[]):

CREATE AGGREGATE array_agg_mult (anyarray) (SFUNC = array_cat, STYPE = 任意数组, INITCOND = '{}');

正如@Lukas 提供的那样，不需要自定义函数 arrayappend().内置的 array_cat() 完成这项工作.但是，这并不能解释为什么您的示例失败，而 Lukas 的答案中的示例有效.相关的区别在于 Lukas 使用 array[d.a] 将数组嵌套到另一个数组层中.

您错误地假设您可以声明类型 int[][].但是你不能:int[][] 与 int[] 对于 PostgreSQL 类型系统是 相同类型.手册中关于数组类型的章节解释说:<块引用>

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

n 维整数数组实际上是 PostgreSQL 中 n-1 维整数数组的数组.您无法从仅定义基本元素的类型中看出这一点.你必须问 array_dims()了解详情.

演示:

SELECT array_agg_mult(arr1) AS arr1 -->一维数组, array_agg_mult(ARRAY[arr1]) AS arr2 -->二维阵列, array_agg_mult(ARRAY[ARRAY[arr1]]) AS arr3 -->3维阵列-  等等.从  (价值观('{1,2,3}'::int[]) -- 一维数组, ('{4,5,6}'), ('{7,8,9}')) t(arr1);

或者:

SELECT array_agg_mult(arr2) AS arr2 -->二维阵列, array_agg_mult(ARRAY[arr2]) AS arr3 -->3维阵列, array_agg(arr2) AS arr3 -->3维阵列；优于 Postgres 9.5+从  (价值观('{{1,2,3}}'::int[]) -- 二维数组,('{{4,5,6}}'),('{{7,8,9}}')) t(arr2);

所有结果列具有相同的类型:int[](即使包含不同数量的维度).

I have a table with arrays of integer.

I want to create an aggregate function that will return a 2-dimensional array with all the rows together. It then gets passed to plr to do some maths on it.

I have:

CREATE OR REPLACE
FUNCTION arrayappend(left int[][], right int[]) 
RETURNS int[] AS 
$BODY$
   SELECT $1 || $2 ;
$BODY$
LANGUAGE SQL;

and:

CREATE AGGREGATE array_sum2 (int[])  (
    SFUNC     = arrayappend,
    STYPE     = int[][],
    INITCOND  = '{}'
);

But the return type is int[], not int[][]?

How can I initialize the aggregate with an empty two dimensional array of integer?

解决方案

Postgres 9.5 or newer

... ships with an additional variant of the aggregate function array_agg(). The manual:

input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or null)

So not exactly the same as the custom aggregate function array_agg_mult() below. But use it, if you can. It's faster.

Related:

• How to sort two dimensional int array in PostgreSQL?

Postgres 9.4 or older

Aggregate function for any array type

With the polymorphic type anyarray it works for all kinds of arrays (including integer[]):

CREATE AGGREGATE array_agg_mult (anyarray) (
   SFUNC     = array_cat
 , STYPE     = anyarray
 , INITCOND  = '{}'
);

As @Lukas provided, the custom function arrayappend() is not needed. The built in array_cat() does the job. However, that doesn't explain why your example fails, while the one in Lukas' answer works. The relevant difference is that Lukas nested the array into another array layer with array[d.a].

You trip over the incorrect assumption that you could declare a type int[][]. But you cannot: int[][] is the same type as int[] for the PostgreSQL type system. The chapter on array types in the manual explains:

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.

An n-dimensional integer array effectively is an array of n-1-dimensional arrays of integer in PostgreSQL. You can't tell that from the type which only defines the base element. You have to ask array_dims() to get the specifics.

To demonstrate:

SELECT array_agg_mult(arr1)               AS arr1  --> 1-dim array
     , array_agg_mult(ARRAY[arr1])        AS arr2  --> 2-dim array
     , array_agg_mult(ARRAY[ARRAY[arr1]]) AS arr3  --> 3-dim array
       -- etc.
FROM  (
   VALUES
      ('{1,2,3}'::int[])                           -- 1-dim array
    , ('{4,5,6}')
    , ('{7,8,9}')
   ) t(arr1);

Or:

SELECT array_agg_mult(arr2)        AS arr2  --> 2-dim array
     , array_agg_mult(ARRAY[arr2]) AS arr3  --> 3-dim array
     , array_agg(arr2)             AS arr3  --> 3-dim array; superior in Postgres 9.5+
FROM  (
   VALUES
      ('{{1,2,3}}'::int[])                  -- 2-dim array
     ,('{{4,5,6}}')
     ,('{{7,8,9}}')
   ) t(arr2);

All resulting columns are of the same type: int[] (even though containing a different number of dimensions).

这篇关于函数中的初始数组聚合多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！