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?

Answer 1

DO $do$ DECLARE    a integer[] := array[1,2,3];    i integer;                      -- int, not bigint BEGIN    FOR i IN 1 .. array_upper(a, 1)    LOOP       RAISE NOTICE '%', a[i];      -- single quotes    END LOOP; END $do$; 

Or simpler with FOREACH in PostgreSQL 9.1 or later:

   FOREACH i IN ARRAY a    LOOP        RAISE NOTICE '%', i;    END LOOP; 

For multi-dimensional arrays 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:

• PostgreSQL: Frequency Table Expansion
• Select each month between a start and end date

Search the tags generate-series or unnest for more.