sql - 将 NULL 数组填充到自定义聚合函数的最大长度

Question

来自问题的回答How to use array_agg() for varchar[] ,

我们可以创建一个自定义聚合函数来聚合 Postgres 中的 n 维数组，例如:

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

一个约束是值必须共享相同的数组范围和相同的长度，处理空值和不同长度是行不通的。

来自答案:

There is no way around that, the array type does not allow such a mismatch in Postgres. You could pad your arrays with NULL values so that all dimensions have matching extents.

我有这样的行

------ arrayfield

-----  {1},
-----  {},
-----  {abc}

array_agg_mult(ARRAY[arrayfield]) AS customarray

我期待像 {{1},NULL,{abc}} 这样的聚合结果

但它会抛出

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing element dimensions are not compatible for concatenation.

有什么方法可以在自定义函数中添加填充值吗？

我发现问题出在数组 length 不同的时候。 {a},{null},{1} 会聚合，但 {a,b},{},{1} 不会。

所以我需要一个可以向现有数组添加 NULL 元素的查询。

一个解决方案是始终附加两个 NULL(2 是该字段中的最大长度)array_cat(arr, ARRAY[NULL,NULL]) 并将数组修剪为长度 2:

   {1}   --> {1,NULL,NULL}     --> {1,NULL}
   {NULL}  --> {NULL,NULL,NULL}  --> {NULL,NULL}
   {abc, def}  --> {abc,def,NULL,NULL}  --> {abc, def} 

但我无法理解语法。

Answer 1

使用自定义聚合函数 array_agg_mult() 就像这个相关答案中定义的那样:

• Selecting data into a Postgres array

你的预期结果是不可能的:

<strike>{{1},NULL,{abc}}</strike>

必须是:

{{1},{NULL},{abc}}

具有 0 个或 1 个数组元素的简单情况

对于只替换空数组的简单情况:您可以通过以下方式实现:

WITH t(arr) AS (
    VALUES
      ('{1}'::text[])
     ,('{}')
     ,('{abc}')
   )
SELECT array_agg_mult(ARRAY[CASE WHEN arr = '{}' THEN '{NULL}' ELSE arr END])
FROM   t;

n个元素的动态填充

使用 array_fill() 将 NULL 元素填充到最大长度的数组:

SELECT array_agg_mult(ARRAY[
         arr || array_fill(NULL::text
                         , ARRAY[max_elem - COALESCE(array_length(arr, 1), 0)])
       ]) AS result
FROM   t, (SELECT max(array_length(arr, 1)) AS max_elem FROM t) t1;

仍然只适用于一维基本数组。

解释

• 子查询 t1 计算基本一维数组的最大长度。
• COALESCE(array_length(arr, 1), 0) 计算该行中数组的长度。
  对于 NULL，COALESCE 默认为 0。
• 使用 array_fill() 生成长度差异的填充数组。
• 使用 || 将其附加到 arr
• 像上面那样使用 array_agg_mult() 进行聚合。

SQL Fiddle.演示所有。
_{SQL Fiddle 中的输出具有误导性，因此我将结果转换为文本。}