在函数中初始化用于聚合多维数组的初始数组。

Question

我有一个整数数组表。

我想创建一个聚合函数，将所有行合并为一个二维数组。然后将其传递给 plr 进行一些运算。

当前的代码如下：

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

并且：

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

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

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

- finlayt

Answer 1

Postgres 9.5或更高版本

... 额外提供了一个变种的聚合函数array_agg()。 手册：

将输入数组连接成一个更高维度的数组（输入必须具有相同的维度，并且不能是空或null）

因此，它与下面的自定义聚合函数array_agg_mult()并不完全相同。但是如果可以使用它，它会更快。

相关：

• 如何在PostgreSQL中对二维int数组进行排序？

Postgres 9.4或更早版本

任何数组类型的聚合函数

使用多态类型anyarray可以适用于各种数组类型(包括integer[])：

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

如@Lukas所提供的，自定义函数arrayappend()是不需要的。内置函数array_cat()已经可以胜任了。然而，这并不能解释为什么你的示例失败了，而Lukas答案中的那个可以工作。相关的区别是Lukas用array[d.a]将数组嵌套到另一个数组层中。

你犯了一个错误的假设，认为你可以声明一个类型int[][]。但是你不能：对于PostgreSQL类型系统，int[][]与int[]是相同的类型。 手册中关于数组类型的章节有解释：

当前的实现也不会强制执行所声明的维数。特定元素类型的数组都被认为是相同类型的，无论大小或维数如何。因此，在CREATE TABLE中声明数组大小或维数只是文档说明；它不影响运行时行为。

在PostgreSQL中，一个n维整数数组实际上是一个n-1维整数数组的数组。您无法从类型中看出这一点，因为类型只定义了基本元素。您必须使用array_dims()来获取具体信息。

为了演示：

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

或者：

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

所有生成的列都是相同的类型：int[]（尽管包含不同数量的维度）。

- Erwin Brandstetter

Answer 2

使用内置的array_cat函数可以实现。

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

测试：

select array_sum2(array[d.a]) from (select array[1,1,2,3] as a union select array[5,8,13,21] as a) d;
       array_sum2        
-------------------------
 {{1,1,2,3},{5,8,13,21}}

- Lukas Eklund