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;
Advertisement
Answer
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: