Iterating Over Multidimensional Arrays in PL/pgSQL: A Comparative Analysis of FOREACH and FOR Loops

Core Challenges in Multidimensional Array Iteration

When working with multidimensional arrays in PL/pgSQL, developers often face the challenge of accessing array elements individually. This is particularly relevant when array elements need to be passed as parameters to other functions. PostgreSQL's array type system treats multidimensional arrays as nested one-dimensional arrays, which influences the choice of iteration method.

FOREACH Loop: The Modern Solution

Introduced in PostgreSQL 9.1, the FOREACH loop is specifically designed to handle array slices. This construct allows specifying the number of dimensions for each slice, enabling precise control over the portion of the array accessed in each iteration.

DO
$do$
DECLARE
   m   text[];
   arr text[] := '{{key1,val1},{key2,val2}}';
BEGIN
   FOREACH m SLICE 1 IN ARRAY arr
   LOOP
      RAISE NOTICE 'another_func(%,%)', m[1], m[2];
   END LOOP;
END
$do$;

In this example, SLICE 1 specifies that each iteration retrieves a one-dimensional slice, meaning the m variable successively receives ['key1','val1'] and ['key2','val2']. Elements within the slice can be accessed via indices m[1] and m[2], making it suitable for passing directly to functions like another_func.

Traditional FOR Loop: Compatibility Approach

For PostgreSQL 9.0 or earlier versions, a FOR loop based on array bounds is necessary. This method uses the array_lower() and array_upper() functions to determine the index range.

DO
$do$
DECLARE
   arr text[] := array[['key1','val1'],['key2','val2']];
BEGIN
   FOR i IN array_lower(arr, 1) .. array_upper(arr, 1)
   LOOP
      RAISE NOTICE 'another_func(%,%)', arr[i][1], arr[i][2];
   END LOOP;
END
$do$;

Here, i iterates over the indices of the first dimension, with second-dimension elements accessed via double indexing arr[i][1] and arr[i][2]. Although slightly more verbose, this approach provides a reliable iteration mechanism for older versions.

Specificities of the Array Type System

It is important to note that PostgreSQL's type system does not strictly distinguish between text[] and text[][]. Multidimensional arrays are essentially nested one-dimensional arrays, meaning that dimension annotations in declarations are more semantic than type-enforced. This design impacts array initialization and iteration logic, requiring developers to understand the actual storage structure of arrays.

Comparison of Array Initialization Methods

The examples illustrate two array initialization methods: the array literal '{{key1,val1},{key2,val2}}' and the array constructor array[['key1','val1'],['key2','val2']]. Both are functionally equivalent, but the literal is more concise, while the constructor offers greater flexibility for dynamically generating arrays. The choice depends on the specific scenario and code readability requirements.

Performance and Best Practices Recommendations

For PostgreSQL 9.1 and later, the FOREACH loop is preferred due to its clear syntax and optimization for array iteration. The traditional FOR loop remains valuable for backward compatibility or complex slicing logic. In practice, it is advisable to clearly define array dimensions and test edge cases to ensure iteration logic correctly handles all elements.