SQL 将选择查询的输出存储在 postgres 的一个数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6402043/
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
Store select query's output in one array in postgres
提问by mitesh
My code is:
我的代码是：
SELECT column_name
FROM information.SCHEMA.columns
WHERE table_name = 'aean'
It returns column names of table aean.
Now I have declared an array:
它返回 table 的列名aean。
现在我已经声明了一个数组：
DECLARE colnames text[]
How can I store select's output in colnames array.
Is there any need to initialize colnames?
如何将 select 的输出存储在 colnames 数组中。
是否需要初始化列名？
回答by Denis de Bernardy
There are two ways. One is to aggregate:
有两种方法。一种是聚合：
SELECT array_agg(column_name::TEXT)
FROM information.schema.columns
WHERE table_name = 'aean'
The other is to use an array constructor:
另一种是使用数组构造函数：
SELECT ARRAY(
SELECT column_name
FROM information.schema.columns
WHERE table_name = 'aean')
I'm presuming this is for plpgsql. In that case you can assign it like this:
我假设这是针对 plpgsql 的。在这种情况下，您可以像这样分配它：
colnames := ARRAY(
SELECT column_name
FROM information.schema.columns
WHERE table_name='aean'
);
回答by ptski
I had exactly the same problem. Just one more working modification of the solution given by Denis (the type must be specified):
我遇到了完全相同的问题。Denis 给出的解决方案的另一个工作修改（必须指定类型）：
SELECT ARRAY(
SELECT column_name::text
FROM information_schema.columns
WHERE table_name='aean'
)
