PL / pgSQL SELECT to array

Question

Here is my function declaration and body part:

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 be an int array, which can then be used in an UPDATE . This function gives me errors like this:

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

Answer 1

Faster and easier with the FROM in the 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; 

---

Aside, when working with an array, the WHERE should be

 WHERE team_id = ANY (team_ids) 

The IN construct works with sets, not arrays.

Answer 2

To create an array from SELECT :

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

The IN statement is documented as having a subquery for the right 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 putting an array or select from team_ids .