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