在PostgreSQL中更改方括号以进行数组聚合

Question

我希望把以下示例中的花括号 "{" 和 "}" 改为 "[" 和 "]"。 我有一个表 A，其中有两列，一列是字符串类型的 text_1，另一列是 bigint 类型的计数。 我想要做的是返回一个矩阵符号，如 [[1,2,4],[2,4,5],...]。

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

WITH B AS(
SELECT 
    array_agg(count) AS count 
FROM
    A 
GROUP BY
    text_1
)
SELECT
    array_agg_mult(ARRAY[count]) 
FROM
    B;

除了如何更新array_agg_mult之外，如果我尝试将INITCOND = '{}'更改为INITCOND = '[]'，我会遇到以下错误：

ERROR: function "array_agg_mult" already exists with same argument types

也许可以通过在postgres中使用json生成来找到一个聪明的解决方案。

- StellaMaris

Answer 1

那些括号与聚合无关，它们只是数组类型的输出格式。新的聚合函数不会改变这一点。

您可以通过简单地执行

select array[1,2,3]

它将显示：

array  
-------
{1,2,3}

INITCOND中的'{}'表示“空数组”。详见手册：https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-IO。

要改变这种显示方式的唯一方法是更改一般数组的输出方法。我认为这不能在未经Postgres源代码修改的情况下完成（这可能会破坏许多东西）。

如果您想以不同的格式显示数组，请编写自己的函数来实现。

create function format_array(p_array anyarray)
  returns text
as
$$
  select translate(p_array::text, '{}', '[]');
$$
language sql;

然后你可以在任何数组上使用它：

select format_array(array[1,2,3]), 
       format_array('{}'::int[]), 
       format_array(array[[1,2,3],[4,5,6]]);

将输出：

format_array | format_array | format_array     
-------------+--------------+------------------
[1,2,3]      | []           | [[1,2,3],[4,5,6]]

当然，您可以在format_array()函数中应用更多的“化妆品”，而不仅仅是用[]替换{}。

---

那么您的查询将变为：

WITH B AS
(
  SELECT array_agg(count) AS count 
  FROM A 
  GROUP BY text_1
)
SELECT format_array(array_agg_mult(ARRAY[count]))
FROM B;

- user330315

---

谢谢您提前回答。正如您所说，括号仅用于显示数组。如何创建一个使用“ [”和“]”而不是“ {”和“}”的结果？我能否通过使用其他函数（例如array_to_json）避免创建format_array函数，该函数也返回用括号包围的字符串。 - StellaMaris

@StellaMaris：你总是需要一些函数来格式化输出。自己创建一个可以格式化所有你想要的内容的函数有什么问题吗？ - user330315

没什么问题，我只是在寻找一个能够输出所需结果的现有函数。回到你的函数上，我并不真正理解函数头中的参数 p_array anyarray。p_array 是 anyarray 的类型吗？我找不到任何关于它的文档资料。 - StellaMaris

p_array 是参数的名称：https://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS - user330315

我明白了，anyarray是数据类型。谢谢。 - StellaMaris

Answer 2

你可以使用内置函数array_to_json将数组格式化为带有方括号的形式，例如：

with
data as (
    select array[
        array[1,2,3],
        array[4,5,6],
        array[7,8,9]
    ] as a
)
select
    a as array_original,
    array_to_json(a) as array_formatted
from data;

/*
Returns:
array_original : {{1,2,3},{4,5,6},{7,8,9}}
array_formatted: [[1,2,3],[4,5,6],[7,8,9]]
*/

- michotross