CODEWITHSUNDEEP
arrayspostgresqlloopsplpgsqlpostgresql-8.4
Dipro Sen
Dipro Sen

Reputation: 4670

Iterating over integer[] in PL/pgSQL

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?

Upvotes: 73

Views: 91636

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can use a FOREACH statement to iterate the 1D array of INT[] as shown below:

DO $$
DECLARE
  temp INT;
  _1d_arr INT[] := ARRAY[1,2,3];
BEGIN
  FOREACH temp SLICE 0 IN ARRAY _1d_arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Or, you can use a FOR statement to iterate the 1D array of INT[] as shown below:

DO $$
DECLARE
  _1d_arr INT[] := ARRAY[1,2,3];
BEGIN
  FOR num IN 1..3 LOOP
    RAISE INFO '%', _1d_arr[num];
  END LOOP;
END
$$;

Then, you can iterate the 1D array of INT[] as shown below:

INFO:  1
INFO:  2
INFO:  3
DO

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

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:

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

Upvotes: 133

Related Questions