当列数据类型是复合类型时，如何使用PostgreSQL在数组中传输列?

I'm using PostgreSQL 9.4 and I'm currently trying to transfer a columns values in an array. For "normal" (not user defined) data types I get it to work.

我正在使用PostgreSQL 9.4，并且目前正在尝试在数组中传输列值。对于“普通”(非用户定义的)数据类型，我让它工作。

To explain my problem in detail, I made up a minimal example. Let's assume we define a composite type "compo" and create a table "test_rel" and insert some values. Looks like this and works for me:

为了详细解释我的问题，我编造了一个极小的例子。假设我们定义了复合类型“compo”，并创建了一个表“test_rel”并插入一些值。看起来像这样，对我很有效:

    CREATE TYPE compo AS(a int, b int);
    CREATE TABLE test_rel(t1 compo[],t2 int);
    INSERT INTO test_rel VALUES('{"(1,2)"}',3);
    INSERT INTO test_rel VALUES('{"(4,5)","(6,7)"}',3);

Next, we try to get an array with column t2's values. The following also works:

接下来，我们尝试获得一个具有列t2值的数组。以下工作:

    SELECT array(SELECT t2 FROM test_rel WHERE t2='3');

Now, we try to do the same stuff with column t1 (the column with the composite type). My problem is now, that the following does'nt work:

现在，我们尝试对t1列(具有复合类型的列)做同样的事情。我现在的问题是，下面的方法不管用:

    SELECT array(SELECT t1 FROM test_rel WHERE t2='3');
    ERROR:  could not find array type for data type compo[]

Could someone please give me a hint, why the same statement does'nt work with the composite type? I'm not only new to *, but also to PostgreSQL and plpgsql. So, please tell me, when I'm doing something the wrong way.

有人能给我一个提示吗，为什么同一个语句不能用于复合类型?我不仅是*的新手，也是PostgreSQL和plpgsql的新手。所以，请告诉我，当我做错事的时候。

1 个解决方案

#1

There were some discussion about this in the PostgreSQL mailing list.

在PostgreSQL邮件列表中有一些讨论。

Long story short, both

长话短说,两者兼而有之

select array(select array_type from ...)
select array_agg(array_type) from ...

represents a concept of array of arrays, which PostgreSQL doesn't support. PostgreSQL supports multidimensional arrays, but they have to be rectangular. F.ex. ARRAY[[0,1],[2,3]] is valid, but ARRAY[[0],[1,2]] is not.

表示数组的概念，而PostgreSQL不支持这个概念。PostgreSQL支持多维数组，但它们必须是矩形的。F.ex。ARRAY[[0,1],[2、3]]是有效的,但是数组([0],[1、2]]。

There were some improvement with both the array constructor & the array_agg() function in 9.5.

9.5中数组构造函数和array_agg()函数都有一些改进。

Now, they explicitly states, that they will accumulate array arguments as a multidimensional array, but only if all of its parts have equal dimensions.

现在，它们明确地声明，它们将以多维数组的形式积累数组参数，但前提是它的所有部分都具有相同的维数。

array() constructor: If the subquery's output column is of an array type, the result will be an array of the same type but one higher dimension; in this case all the subquery rows must yield arrays of identical dimensionality, else the result would not be rectangular.

array()构造函数:如果子查询的输出列是数组类型，那么结果将是一个具有相同类型但具有更高维度的数组;在这种情况下，所有子查询行都必须生成具有相同维度的数组，否则结果将不是矩形的。

array_agg(any array type): input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)

array_agg(任何数组类型):将输入数组连接到一个更高维度的数组中(输入必须具有相同的维度，不能为空或为空)

For 9.4, you could wrap the array into a row: this way, you could create something, which is almost an array of arrays:

对于9.4，你可以将数组包装成一行:这样，你可以创建一个几乎是数组的东西:

SELECT array(SELECT ROW(t1) FROM test_rel WHERE t2='3');
SELECT array_agg(ROW(t1)) FROM test_rel WHERE t2='3';

Or, you could use a recursive CTE (and an array concatenation) to workaround the problem, like:

或者，您可以使用递归CTE(和数组连接)来解决这个问题，比如:

with recursive inp(arr) as (
  values (array[0,1]), (array[1,2]), (array[2,3])
),
idx(arr, idx) as (
  select arr, row_number() over ()
  from   inp
),
agg(arr, idx) as (
    select array[[0, 0]] || arr, idx
    from   idx
    where  idx = 1
  union all
    select agg.arr || idx.arr, idx.idx
    from   agg
    join   idx on idx.idx = agg.idx + 1
)
select arr[array_lower(arr, 1) + 1 : array_upper(arr, 1)]
from agg
order by idx desc
limit 1;

But of course this solution is highly dependent of your data ('s dimensions).

但是，当然，这个解决方案高度依赖于您的数据(其维度)。

#1