将数据选择到Postgres数组中

Question

我有如下数据:

name          id             url

John          1              someurl.com
Matt          2              cool.com
Sam           3              stackoverflow.com

我该如何在Postgres中编写SQL语句，以将这些数据选择到一个多维数组中，例如：

{{John, 1, someurl.com}, {Matt, 2, cool.com}, {Sam, 3, stackoverflow.com}}

我之前在Postgres中看到过这种数组用法，但不知道如何将数据从表中选择为这种数组格式。

假设这里所有的列都是text类型。

- harman_kardon

Answer 1

在 PostgreSQL 9.4 及之前的版本中，无法使用 array_agg() 生成多维数组。
（但即将推出的 Postgres 9.5 提供了一个 新变体的 array_agg() 可以！）

从 @Matt Ball 的查询 中获得的是记录的数组 (the_table[])。

一个数组只能包含相同基本类型的元素。由于您显然有数字和字符串类型，因此请将所有列（尚未）转换为 text 类型才能使其正常工作。

您可以像我之前在这里演示的那样创建一个聚合函数来实现这一点。

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

呼叫：

SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM   tbl;

注意额外的ARRAY[]层，使其成为一个多维数组（更准确地说是二维数组）。

即时演示：

WITH tbl(id, txt) AS (
    VALUES
      (1::int, 'foo'::text)
     ,(2,      'bar')
     ,(3,      '}b",') -- txt has meta-characters
    )
    , x AS (
    SELECT array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS t
    FROM   tbl
    )
SELECT *, t[1][3] AS arr_element_1_1, t[3][4] AS arr_element_3_2
FROM   x;

- Erwin Brandstetter

Answer 2

你需要使用聚合函数，array_agg 可以满足你的需求。请查看聚合函数文档了解更多信息。

SELECT array_agg(s) FROM (SELECT name, id, url FROM the_table ORDER BY id) AS s;

- Matt Ball

---

此答案返回的是记录数组，而不是多维数组。 - Erwin Brandstetter

是的 - 刚刚在上下文中工作了，你说得对... 我真的需要多维数组！ - harman_kardon

@MattW：我认为可以帮到你。:) 已发布答案。 - Erwin Brandstetter