PL/pgSQL SELECT into an array
plpgsql, postgresql, postgresql-9.1, sql
Solution
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:
- How to use ANY instead of IN in a WHERE clause with Rails?
Problem
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; ```