sql - 如何将 postgres 中的可变长度记录转换为数组？

Question

我正在尝试动态创建表并将它们的列转换为数组。所以我最终会得到一个只有 1 列的数组类型的表。数组的长度将等于输入中的列数。如果我事先知道我的(动态创建的)表有哪些列，解决方案会很简单，但我不知道这一点。

我试过使用数组函数。如果您事先知道您的表包含哪些列，则此方法有效。如果您不知道这一点(因为表是动态定义的)，则必须使用星号来获取所有列。但是 array 函数然后创建一个只有 1 个元素的数组，包含所有列。这不是我想要的。

CREATE TEMPORARY TABLE my_addresses(
  personname TEXT NOT NULL, 
  streetname TEXT NOT NULL, 
  country_id TEXT
);
------------------------------------------------------------------------
INSERT INTO my_addresses 
VALUES(  'Bill Gates', 'Somewhere Avenue', 'US'),
        ('Linus Thorvalds', 'Other Road', 'US'),
        ('Erwin Brandstetter', 'Solution Street', 'AT'); --it's a tribute :-)
------------------------------------------------------------------------
SELECT 
    cardinality(ARRAY[  ma.personname, 
                        ma.streetname, 
                        ma.country_id]), --an array of 3 elements is created, as you would expect
    cardinality(ARRAY[ma.*]) --an array of 1 element is created, which is not what I want
FROM my_addresses   AS ma;

顺便说一句，这是我在 stackoverflow 上发表的第一篇文章(请原谅我的任何格式错误)。我使用 Postgres 版本 9.6.14。

Answer 1

这看起来很奇怪。但与涉及整个记录的许多挑战一样，解决方案通常是使用 JSON。在这种情况下:

SELECT (SELECT array_agg(t.v)
        FROM jsonb_each_text(to_jsonb(ma.*)) t(k, v)
       ) as myarray
FROM my_addresses ma;

Here是一个数据库<> fiddle 。

无论如何，您可能会发现 JSON 是解决您最终问题的更好解决方案。但是这个问题特别询问数组。