How to select multiple values into an array and loop through? (postgres 9.3)

I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a transaction.

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array; 
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$ LANGUAGE plpgsql;

Table:

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 id            | integer | 
 other_id      | integer | 
 id_copy       | integer | 
 other_id_copy | integer | 
 looped        | boolean | 

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | 
  6 |       15 |         |               | 
  2 |       11 |         |               | 
  7 |       16 |         |               | 
  3 |       12 |         |               | 
  4 |       13 |         |               | 
  5 |       14 |         |               | 
(7 rows)

When I call select loop_test(), I get the following results:

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

I would, however, like to create a function to select both the id and the other_id into an array. I was told about using something like agg_array, but I don'

A much better way, yet: just update. No loop needed.

UPDATE loop_test
SET    id_copy = id
     , other_id_copy = other_id;
WHERE  id IS NOT NULL;

The WHERE condition is only useful if id can be null and you want a perfect equivalent of what you had.
Loop

If you are just exploring loops - you can assign multiple variables. See:

• SELECT INTO with more than one attribution

CREATE OR REPLACE FUNCTION better_loop_test()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _id int;
   _other_id int;
BEGIN
   -- example makes no sense, just a loop demo
   FOR _id, _other_id IN
      SELECT id, other_id FROM loop_test
   LOOP
      UPDATE loop_test
      SET    id_copy = _id
           , other_id_copy = _other_id
      WHERE id = _id;
   END LOOP;
END
$func$;

While you just need the two columns of known type, that may be a bit cheaper than fetching whole (possibly big) rows.

StackExchange Database Administrators Q#222297, answer score: 10