Iterating over integer[] in PL/pgSQL

ArraysPostgresqlLoopsPlpgsqlPostgresql 8.4

Arrays Problem Overview


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?

Arrays Solutions


Solution 1 - Arrays

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:

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

Search the tags [tag:generate-series] or [tag:unnest] for more.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDipro SenView Question on Stackoverflow
Solution 1 - ArraysErwin BrandstetterView Answer on Stackoverflow