sql - 选择数据到 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。

Answer 1

您不能使用 array_agg() 生成多维数组，至少不能达到 PostgreSQL 9.4。
(但是即将到来的 Postgres 9.5 提供了一个 new variant of array_agg() 可以!)

你从 @Matt Ball's query 中得到了什么是一个记录数组 (the_table[])。

数组只能容纳相同基类型的元素。您显然有数字和字符串类型。将所有列(尚未)转换为 text 以使其工作。

您可以像我向您演示的那样为此创建一个聚合函数 here之前。

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;