postgresql array_agg 用于数组类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6782268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
array_agg for Array Types
提问by Conor B
I'm trying to get array_aggto work with an array type in Postgresql and I'm having trouble figuring out if this is possible and if so how to do it. The pertinent part of my query looks like this:
我正在尝试array_agg在 Postgresql 中使用数组类型，但我无法弄清楚这是否可行，如果可以，该怎么做。我的查询的相关部分如下所示：
array_agg(ARRAY[e.alert_type::text, e.id::text, cast(extract(epoch from e.date_happened) as text)] order by e.date_happened asc, e.id asc)
The error that I'm getting in response is ERROR: could not find array type for data type text[]
我得到的回应错误是 ERROR: could not find array type for data type text[]
Is this possible or should I try to find another approach?
这是可能的还是我应该尝试寻找另一种方法？
Thanks!
谢谢！
采纳答案by Grzegorz Szpetkowski
You could write custom aggregate to handle your specific array of arrays, e.g.:
您可以编写自定义聚合来处理您的特定数组数组，例如：
DROP TABLE IF EXISTS e;
CREATE TABLE e
(
id serial PRIMARY KEY,
alert_type text,
date_happened timestamp with time zone
);
INSERT INTO e(alert_type, date_happened) VALUES
('red', '2011-05-10 10:15:06'),
('yellow', '2011-06-22 20:01:19');
CREATE OR REPLACE FUNCTION array_agg_custom_cut(anyarray)
RETURNS anyarray
AS 'SELECT [2:array_length(, 1)]'
LANGUAGE SQL IMMUTABLE;
DROP AGGREGATE IF EXISTS array_agg_custom(anyarray);
CREATE AGGREGATE array_agg_custom(anyarray)
(
SFUNC = array_cat,
STYPE = anyarray,
FINALFUNC = array_agg_custom_cut,
INITCOND = $${{'', '', ''}}$$
);
Query:
询问：
SELECT
array_agg_custom(
ARRAY[
alert_type::text,
id::text,
CAST(extract(epoch FROM date_happened) AS text)
])
FROM e;
Result:
结果：
array_agg_custom
--------------------------------------------
{{red,1,1305036906},{yellow,2,1308787279}}
(1 row)
EDIT:
编辑：
Here is second, shorter way (that is, you don't need array_agg_custom_cutfunction, but as you see additional ARRAYlevel is necessary in query):
这是第二种更短的方法（也就是说，您不需要array_agg_custom_cut函数，但正如您所见ARRAY，查询中需要额外的级别）：
CREATE AGGREGATE array_agg_custom(anyarray)
(
SFUNC = array_cat,
STYPE = anyarray
);
SELECT
array_agg_custom(
ARRAY[
ARRAY[
alert_type::text,
id::text,
CAST(extract(epoch FROM date_happened) AS text)
]
])
FROM e;
Result:
结果：
array_agg_custom
--------------------------------------------
{{red,1,1305036906},{yellow,2,1308787279}}
(1 row)
回答by girgen
or cast the array to text like array_agg(array[xxx, yyy]::text)
或将数组转换为类似 array_agg(array[xxx, yyy]::text) 的文本
array_agg(ARRAY[e.alert_type::text, e.id::text,
cast(extract(epoch from e.date_happened) as text)]::text
order by e.date_happened asc, e.id asc)
