postgresql PL/pgSQL SELECT 到一个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11731481/
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
PL/pgSQL SELECT into an array
提问by nnyby
Here's my function declaration and part of the body:
这是我的函数声明和正文的一部分：
CREATE OR REPLACE FUNCTION access_update()
RETURNS void AS $$
DECLARE team_ids bigint[];
BEGIN
SELECT INTO team_ids "team_id" FROM "tmp_team_list";
UPDATE "team_prsnl"
SET "updt_dt_tm" = NOW(), "last_access_dt_tm" = NOW()
WHERE "team_id" IN team_ids;
END; $$ LANGUAGE plpgsql;
I want team_idsto be an array of ints that I can then use in the UPDATEstatement. This function give me errors like this:
我想team_ids成为一个整数数组，然后我可以在UPDATE语句中使用它。这个函数给了我这样的错误：
psql:functions.sql:62: ERROR: syntax error at or near "team_ids"
LINE 13: AND "team_id" IN team_ids;
回答by Erwin Brandstetter
Faster and simpler with a FROMclause in your UPDATEstatement:
FROM在您的UPDATE语句中使用子句更快更简单：
UPDATE team_prsnl p
SET updt_dt_tm = now()
,last_access_dt_tm = now()
FROM tmp_team_list t
WHERE p.team_id = t.team_id;
That aside, while operating with an array, the WHEREclause would have to be
除此之外，在使用数组进行操作时，该WHERE子句必须是
WHERE team_id = ANY (team_ids)
The INconstruct works with sets, not with arrays.
该IN构造适用于集合，而不适用于数组。
回答by dsh
To create an array from a SELECT:
从 a 创建数组SELECT：
# select array( select id from tmp_team_list ) ;
?column?
----------
{1,2}
(1 row)
The INoperator is documentedas taking a subquery for the right-hand operand. For example:
该IN运算符被记录为对右侧操作数进行子查询。例如：
UPDATE team_prsnl SET updt_dt_tm = NOW()
WHERE team_id IN (SELECT id FROM tmp_team_list);
Perhaps you can avoid the array altogether, or try supplying the array or select from team_ids.
也许您可以完全避免使用数组，或者尝试提供数组或select from team_ids.
