Iterating over integer[] in PL/pgSQL

Question

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this:

declare
    a integer[] = array[1,2,3];
    i bigint;
begin
    for i in a
loop 
    raise notice "% ",i;
end loop;
return true;
end

In my actual use case the integer array a is passed as parameter to the function. I get this error:

ERROR:  syntax error at or near "$1"
LINE 1:   $1

How to loop through the array properly?

Erwin Brandstetter · Accepted Answer · 2022-05-29T21:49:29.117

Postgres 9.1 added FOREACH to loop over arrays:

DO
$do$
DECLARE
   _arr  int[] := '{1,2,3,4}';
   _elem int;                      -- matching element type
BEGIN
   FOREACH _elem IN ARRAY _arr
   LOOP 
      RAISE NOTICE '%', _elem;
   END LOOP;
END
$do$;

db<>fiddle here

Works for multi-dimensional arrays, too: Postgres flattens the array and iterates over all elements. To loop over slices, see link below.

For older versions:

   FOR i IN 1 .. array_upper(_arr, 1)  -- "i" is the index
   LOOP
      RAISE NOTICE '%', _arr[i];       -- single quotes
   END LOOP;

For multi-dimensional arrays and looping over array slices see:

Loop over array dimension in plpgsql

However, set-based solutions with generate_series() or unnest() are often faster than looping over big sets. Basic examples:

right now I am passing the array to the function as `f(array[1,2,3,4])` is there any better way to pass an array to a function ? — Dipro Sen, Apr 18 '12 at 17:30
`f('{1,2,3,4}'::int[])` is *another* way. Better? - you decide! — Erwin Brandstetter, Apr 18 '12 at 17:33

Iterating over integer[] in PL/pgSQL

1 Answers1

Linked