'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;

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;


Solution 1:[1]

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;

That aside, while operating with an array, the WHERE clause would have to be:

WHERE p.team_id = ANY (team_ids)

The IN construct works with lists or sets, not with arrays. See:

Solution 2:[2]

To create an array from a SELECT:

# select array(  select id from tmp_team_list ) ;
 ?column? 
----------
 {1,2}
(1 row)

The IN operator is documented as taking a subquery for the right-hand operand. For example:

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.

Solution 3:[3]

a tiny customization based on other answers.
If the team_id is a normal int data type.

   UPDATE team_prsnl p
    SET    updt_dt_tm = now()
          ,last_access_dt_tm = now()
    FROM   tmp_team_list t
    WHERE  p.team_id = any(array(select team_id from t));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 dsh
Solution 3 Mark