Iterating over integer[] in PL/pgSQL

by

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:

More Related Contents:

  1. Unnest multiple arrays in parallel
  2. Return rows matching elements of input array in plpgsql function
  3. Loop over array dimension in plpgsql
  4. Unnest array by one level
  5. How to solve while loop in array?
  6. Creating an array from a text file in Bash
  7. Can PostgreSQL index array columns?
  8. Iterate over two arrays simultaneously in bash
  9. Expanding column cells for each column cell
  10. How to iterate over array of objects in Handlebars?
  11. Can PostgreSQL have a uniqueness constraint on array elements?
  12. Compare arrays for equality, ignoring order of elements
  13. Using jsonb_set() for updating specific jsonb array value
  14. How to keep associative array order?
  15. Cursor based records in PostgreSQL
  16. jsonb vs jsonb[] for multiple addresses for a customer
  17. Performance difference between looping range vs looping array
  18. Is indexing vectors in MATLAB inefficient?
  19. Hashing a String to a Numeric Value in PostgreSQL
  20. Loop on tables with PL/pgSQL in Postgres 9.0+
  21. Loop through columns of RECORD
  22. Using local variable outside its scope
  23. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  24. Django JSONField inside ArrayField
  25. PostgreSQL IF statement
  26. Find the 2nd largest element in an array with minimum number of comparisons
  27. In Swift, an efficient function that separates an array into 2 arrays based on a predicate
  28. Turn Excel range into VBA string
  29. Powershell: Piping output of pracl command to array
  30. VBA Excel large data manipulation taking forever

Leave a Comment