postgresql 选择多行作为数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13312067/
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
Select multiple rows as array
提问by user974703
I have a table where two people may have the same name, but separate IDs. I am given the name, and need to request their IDs.
我有一张桌子，两个人可能有相同的名字，但不同的 ID。我得到了名字，需要索取他们的身。
When I use a command like:
当我使用如下命令时：
SELECT id_num INTO cust_id FROM Customers WHERE name=CName;
If I use this command on the command line (psql), it returns 2 results (for example).
如果我在命令行 (psql) 上使用此命令，它会返回 2 个结果（例如）。
But when I use it as part of an SQL script (PL/pgSQL), it always just grabs the first instance.
但是当我将它用作 SQL 脚本 (PL/pgSQL) 的一部分时，它总是只获取第一个实例。
I tried selecting into cust_id[], but that produced an error. So what is the proper way to select ALL results, and pump them into an array or another easy way to use them?
我尝试选择 into cust_id[]，但这产生了错误。那么选择所有结果并将它们泵入数组或其他简单使用方法的正确方法是什么？
回答by sufleR
In declare
在申报
DECLARE id_nums bigint[];
in select
在选择
id_nums := ARRAY(select cust_id from Customers WHERE name = CName);
If you prefer loop use
如果您更喜欢循环使用
DECLARE id_num bigint;
FOR id_num in select cust_id from Customers WHERE name = CName LOOP
your code here
END LOOP;
Read plpgsql control structures in postgresql docs 9.1.
阅读 postgresql 文档9.1 中的plpgsql 控制结构。
回答by Erwin Brandstetter
To put data from individual rows into an array, use an array constructor:
要将单个行中的数据放入数组，请使用数组构造函数：
DECLARE id_nums int[]; -- assuming cust_id is of type int
id_nums := ARRAY (SELECT cust_id FROM customers WHERE name = cname);
Or the aggregate functionarray_agg()
或者聚合函数array_agg()
id_nums := (SELECT array_agg(cust_id) FROM customers WHERE name = cname);
Or use SELECT INTOfor the assignment::
或SELECT INTO用于分配::
SELECT INTO id_nums
ARRAY (SELECT cust_id FROM customers WHERE name = cname);
