PL/pgSQL SELECT into an array

Question

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;

I think you got the order wrong in your select. Shouldn't that be: SELECT team_id INTO team_ids FROM tmp_team_list; — user330315
– user330315, Commented Jul 31, 2012 at 7:26

Erwin Brandstetter · Accepted Answer · 2022-04-16 03:19:12Z

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?

dsh · Accepted Answer · 2012-07-31 00:57:01Z

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.

jian · Accepted Answer · 2021-11-30 09:33:43Z

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

Collectives™ on Stack Overflow

PL/pgSQL SELECT into an array

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related