PL/pgSQL SELECT放入数组 [英] PL/pgSQL SELECT into an array

问题描述

这是我的函数声明和正文的一部分:

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;

我希望team_ids是一个整数数组，然后可以在UPDATE语句中使用它.此功能给我这样的错误:

I want team_ids to be an array of ints that I can then use in the UPDATE statement. This function give me errors like this:

psql:functions.sql:62: ERROR:  syntax error at or near "team_ids"
LINE 13:  AND "team_id" IN team_ids;

推荐答案

使用更快，更简单UPDATE语句中的FROM子句:

Faster and simpler with a FROM clause in your UPDATE statement:

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;

---

此外，在使用数组进行操作时，必须要WHERE子句

WHERE team_id = ANY (team_ids)

IN构造适用于集合，而不适用于数组.

The IN construct works with sets, not with arrays.

这篇关于PL/pgSQL SELECT放入数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！