postgresql 如何将所有记录中的所有整数数组合并到 postgres 中的单个数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22677463/
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
How to merge all integer arrays from all records into single array in postgres
提问by user1465266
I have a column which is of type integer array. How can I merge all of them into a single integer array?
我有一个整数数组类型的列。如何将所有这些合并到一个整数数组中？
For example: If I execute query:
例如：如果我执行查询：
select column_name from table_name
I get result set as:
我得到的结果集为：
-[RECORD 1]----------
column_name | {1,2,3}
-[RECORD 2]----------
column_name | {4,5}
How can I get {1,2,3,4,5}as final result?
我怎样才能得到{1,2,3,4,5}最终结果？
回答by mu is too short
回答by Craig Ringer
Define a trivial custom aggregate:
定义一个简单的自定义聚合：
CREATE AGGREGATE array_cat_agg(anyarray) (
SFUNC=array_cat,
STYPE=anyarray
);
and use it:
并使用它：
WITH v(a) AS ( VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6,7]))
SELECT array_cat_agg(a) FROM v;
If you want a particular order, put it within the aggregate call, i.e. array_cat_agg(a ORDER BY ...)
如果你想要一个特定的订单，把它放在聚合调用中，即 array_cat_agg(a ORDER BY ...)
This is roughly O(n log n)for n rows (I think). For better performance you'd need to write it in C, where you can use the more efficient (but horrible to use) C API for PostgreSQL arrays to avoid re-copying the array each iteration.
这大约O(n log n)是 n 行（我认为）。为了获得更好的性能，您需要用 C 编写它，您可以在其中使用更高效（但使用起来很糟糕）的 C API 来处理 PostgreSQL 数组，以避免每次迭代都重新复制数组。
回答by Vladimir Aleshin
You could use lateral subqueryfor that:
你可以使用lateral subquery：
select array_agg(u.a)
from (values (array[1, 2, 3]), (array[4, 5])) t (a)
join lateral unnest(t.a) u (a) on true;
回答by Patrick
The only way you can do this is inside a function:
您可以执行此操作的唯一方法是在函数内部：
CREATE FUNCTION merge_arrays() RETURNS int[] AS $$
DECLARE
this record;
res int[];
BEGIN
FOR this IN
SELECT column_name FROM table_name
LOOP
array_cat(res, this.column_name);
END LOOP;
RETURN res;
END; $$ LANGUAGE plpgsql;
Then you can
那么你也能
SELECT merge_arrays();
to get the result you are looking for.
以获得您正在寻找的结果。
This of course hard-codes your table definition into the function, which may (or may not) be an issue. In addition, you may want to put a WHEREclause in the loop query to restrict the records whose arrays you want to append; you might use an additional function parameter to do this.
这当然会将您的表定义硬编码到函数中，这可能（也可能不是）是一个问题。此外，您可能希望WHERE在循环查询中放置一个子句来限制要追加其数组的记录；您可以使用附加的函数参数来执行此操作。
Keep in mind that you might get a really large array as your table increases in size and that may affect performance. Do you really need all sub-arrays from all records in one large array? Have a look at your application and see if you can do the merge at that level, rather than in a single query.
请记住，随着表大小的增加，您可能会得到一个非常大的数组，这可能会影响性能。你真的需要一个大数组中所有记录的所有子数组吗？查看您的应用程序，看看您是否可以在该级别而不是在单个查询中进行合并。
