FOREACH for array iteration in PostgreSQL - Deep Dive

Overview - FOREACH for array iteration

What is it?

FOREACH is a control structure in PostgreSQL used to loop through each element of an array. It allows you to perform actions on each item inside the array one by one. This helps when you want to process or analyze all elements without writing repetitive code. It is especially useful in PL/pgSQL functions and procedures.

Why it matters

Without FOREACH, handling arrays in PostgreSQL would require manual indexing or complex loops, which can be error-prone and harder to read. FOREACH simplifies array processing, making code cleaner and easier to maintain. This improves productivity and reduces bugs when working with collections of data inside the database.

Where it fits

Before learning FOREACH, you should understand basic SQL queries, arrays in PostgreSQL, and simple PL/pgSQL control structures like loops and conditionals. After mastering FOREACH, you can explore more advanced array functions, set-returning functions, and performance optimization techniques for array processing.

Mental Model

Core Idea

FOREACH lets you visit each item in an array one at a time to perform actions, just like reading a list from start to end.

Think of it like...

Imagine you have a basket of apples and you want to check each apple for freshness. Instead of grabbing apples randomly, you pick one apple at a time from the basket and inspect it carefully before moving to the next. FOREACH works the same way with array elements.

┌─────────────┐
│   ARRAY     │
│ [a, b, c]   │
└─────┬───────┘
      │
      ▼
┌─────────────┐
│ FOREACH LOOP│
│ 1st: a      │
│ 2nd: b      │
│ 3rd: c      │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding PostgreSQL Arrays

Concept: Learn what arrays are in PostgreSQL and how they store multiple values in a single column.

In PostgreSQL, an array is a data type that can hold multiple values of the same type, like integers or text, inside one column. For example, you can have an integer array: {1,2,3} or a text array: {'apple','banana','cherry'}. Arrays let you group related data together easily.

Result

You can store and retrieve multiple values in one column using arrays.

Knowing arrays is essential because FOREACH operates directly on these collections to process each element.

2

Foundation

Basics of PL/pgSQL Loops

Concept: Learn how to write simple loops in PostgreSQL's procedural language to repeat actions.

PL/pgSQL supports loops like LOOP, WHILE, and FOR to repeat commands. For example, a FOR loop can count from 1 to 5 and perform an action each time. Loops help automate repetitive tasks inside functions.

Result

You can write code that repeats actions multiple times inside the database.

Understanding loops prepares you to use FOREACH, which is a specialized loop for arrays.

3

Intermediate

Using FOREACH to Iterate Arrays

🤔Before reading on: do you think FOREACH requires manual index handling or automatically processes each element? Commit to your answer.

Concept: FOREACH automatically loops through each element of an array without needing manual index tracking.

FOREACH syntax looks like this: FOREACH element_variable IN ARRAY array_variable LOOP -- actions using element_variable END LOOP; This runs the loop body once for each element in the array, assigning the current element to element_variable.

Result

Each array element is processed in order without extra code for indexing.

Knowing FOREACH handles indexing internally reduces errors and simplifies array processing code.

4

Intermediate

FOREACH with Different Data Types

🤔Before reading on: do you think FOREACH works only with text arrays or with any array type? Commit to your answer.

Concept: FOREACH works with arrays of any data type supported by PostgreSQL, such as integers, text, or custom types.

You can use FOREACH with integer arrays, text arrays, or even arrays of composite types. For example: DECLARE nums integer[] := ARRAY[10, 20, 30]; num integer; BEGIN FOREACH num IN ARRAY nums LOOP RAISE NOTICE 'Number: %', num; END LOOP; END; This prints each number in the array.

Result

FOREACH processes each element regardless of its type.

Understanding type flexibility makes FOREACH a powerful tool for many array processing tasks.

5

Intermediate

FOREACH with Slice and Reverse Options

🤔Before reading on: do you think FOREACH can iterate arrays backwards or only forwards? Commit to your answer.

Concept: FOREACH supports options to iterate over a slice of the array or in reverse order.

You can specify a slice of the array to loop over using the SLICE option, or iterate backwards using REVERSE. For example: FOREACH elem IN ARRAY arr SLICE 2..4 LOOP -- process elements 2 to 4 END LOOP; FOREACH elem IN ARRAY arr REVERSE LOOP -- process elements from last to first END LOOP;

Result

You can control which parts of the array to process and the direction of iteration.

Knowing these options lets you write more precise and flexible array loops.

6

Advanced

FOREACH in Real-World Functions

🤔Before reading on: do you think FOREACH is only for simple tasks or can it handle complex logic inside loops? Commit to your answer.

Concept: FOREACH can be used inside complex PL/pgSQL functions to process arrays with conditional logic and database operations.

In production, you might use FOREACH to update multiple rows, validate data, or build dynamic queries. For example: FOREACH item IN ARRAY input_array LOOP IF item IS NOT NULL THEN PERFORM some_function(item); END IF; END LOOP; This shows how FOREACH integrates with other logic.

Result

FOREACH enables powerful, readable array processing inside database functions.

Understanding how to combine FOREACH with conditions and queries unlocks practical database programming.

7

Expert

Performance and Internals of FOREACH

🤔Before reading on: do you think FOREACH creates a copy of the array or works directly on it? Commit to your answer.

Concept: FOREACH operates directly on the array without copying it, and its performance depends on array size and loop body complexity.

Internally, FOREACH uses PostgreSQL's array access functions to fetch elements one by one. It does not duplicate the array, which saves memory. However, large arrays or complex loop bodies can impact performance. Using FOREACH with REVERSE or SLICE does not add significant overhead.

Result

FOREACH is efficient for array iteration but should be used thoughtfully with large data.

Knowing FOREACH's internal behavior helps optimize database functions and avoid performance pitfalls.

Under the Hood

FOREACH uses PostgreSQL's internal array subscripting functions to access each element sequentially. It sets the loop variable to the current element and executes the loop body. The array is not copied; instead, elements are fetched on demand. The loop can handle NULL elements and supports options like SLICE and REVERSE by adjusting the index range and direction internally.

Why designed this way?

FOREACH was designed to simplify array processing in PL/pgSQL by abstracting manual index management. This reduces programmer errors and improves code clarity. Alternatives like manual FOR loops with indexes were more error-prone and verbose. The design balances ease of use with performance by avoiding unnecessary array copying.

┌─────────────┐
│ Input Array │
│ [e1, e2, e3]│
└─────┬───────┘
      │
      ▼
┌─────────────┐
│ FOREACH     │
│ Loop Start  │
├─────────────┤
│ Index i=1   │
│ element=e1  │
│ Execute body│
│ Index i=2   │
│ element=e2  │
│ Execute body│
│ Index i=3   │
│ element=e3  │
│ Execute body│
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does FOREACH require you to manually track the current index of the array? Commit to yes or no.

Common Belief: FOREACH requires manual handling of array indexes to access elements.

Reality: FOREACH automatically manages the current element internally and does not require manual index tracking.

Why it matters: Believing you must track indexes leads to more complex and error-prone code, defeating FOREACH's purpose.

Quick: Can FOREACH modify the original array elements directly? Commit to yes or no.

Common Belief: FOREACH can change the original array elements during iteration.

Reality: FOREACH only reads elements; it cannot modify the original array in place during iteration.

Why it matters: Expecting in-place modification can cause confusion and bugs; to change arrays, you must build a new array or update separately.

Quick: Does FOREACH create a copy of the array before looping? Commit to yes or no.

Common Belief: FOREACH duplicates the entire array internally before processing.

Reality: FOREACH accesses the original array elements directly without copying the array.

Why it matters: Thinking FOREACH copies arrays may cause unnecessary concern about memory usage or performance.

Quick: Is FOREACH limited to text arrays only? Commit to yes or no.

Common Belief: FOREACH only works with text arrays in PostgreSQL.

Reality: FOREACH works with arrays of any data type supported by PostgreSQL.

Why it matters: Limiting FOREACH to text arrays restricts its usefulness and leads to missed opportunities for code reuse.

Expert Zone

1

FOREACH does not support modifying the array elements directly; to change elements, you must build a new array or use other functions.

2

Using FOREACH with large arrays can impact performance; sometimes set-based SQL operations are more efficient.

3

FOREACH's SLICE and REVERSE options allow fine control over iteration, but combining them requires careful index understanding.

When NOT to use

FOREACH is not ideal when you need to perform set-based operations that can be done with SQL queries directly, as set operations are usually faster. Also, avoid FOREACH for very large arrays where performance is critical; consider unnesting arrays and using joins instead.

Production Patterns

In production, FOREACH is commonly used inside PL/pgSQL functions to validate array inputs, perform row-by-row processing, or call other functions per element. It is often combined with conditional logic and exception handling to build robust data processing routines.

Connections

Set-Based SQL Operations

FOREACH is a procedural alternative to set-based operations that work on entire tables or arrays at once.

Understanding FOREACH helps appreciate when to use procedural loops versus set-based queries for better performance and clarity.

Iterator Pattern (Software Design)

FOREACH embodies the iterator pattern by providing a way to access elements sequentially without exposing the underlying structure.

Recognizing FOREACH as an iterator helps understand its role in abstracting array traversal in database programming.

Assembly Line Processing (Manufacturing)

Like an assembly line processes items one by one, FOREACH processes array elements sequentially to apply operations.

Seeing FOREACH as an assembly line clarifies how each element is handled individually in order.

Common Pitfalls

#1 Trying to modify array elements directly inside FOREACH loop.

Wrong approach:

FOREACH elem IN ARRAY arr LOOP
elem := elem + 1; -- attempt to change element
END LOOP;

Correct approach:

DECLARE
new_arr integer[] := '{}';
elem integer;
BEGIN
FOREACH elem IN ARRAY arr LOOP
new_arr := array_append(new_arr, elem + 1);
END LOOP;
END;

Root cause:

Misunderstanding that FOREACH loop variable is a copy, not a reference to the original array element.

#2 Using FOREACH on a NULL array without checking.

Wrong approach:

FOREACH elem IN ARRAY null_array LOOP
RAISE NOTICE '%', elem;
END LOOP;

Correct approach:

IF null_array IS NOT NULL THEN
FOREACH elem IN ARRAY null_array LOOP
RAISE NOTICE '%', elem;
END LOOP;
END IF;

Root cause:

Not handling NULL arrays causes runtime errors because FOREACH cannot iterate over NULL.

#3 Manually managing indexes inside FOREACH loop.

Wrong approach:

DECLARE
i integer := 1;
BEGIN
FOREACH elem IN ARRAY arr LOOP
RAISE NOTICE 'Index %: %', i, elem;
i := i + 1;
END LOOP;
END;

Correct approach:

FOR i IN array_lower(arr,1)..array_upper(arr,1) LOOP
RAISE NOTICE 'Index %: %', i, arr[i];
END LOOP;

Root cause:

Confusing FOREACH with FOR loops that use indexes; FOREACH does not provide index tracking.

Key Takeaways

FOREACH is a simple and powerful way to loop through each element of an array in PostgreSQL without manual indexing.

It works with arrays of any data type and supports options to iterate slices or reverse order.

FOREACH loop variables are copies of elements; you cannot modify the original array elements directly inside the loop.

Using FOREACH improves code readability and reduces errors compared to manual array processing.

Understanding when to use FOREACH versus set-based operations is key for writing efficient database functions.

Practice

1. What is the main purpose of the FOREACH statement in PostgreSQL when working with arrays?

easy

A. To convert an array into a string.

B. To loop through each element of an array easily inside PL/pgSQL.

C. To sort the elements of an array in ascending order.

D. To create a new array from existing tables.

Solution

1. Step 1: Understand FOREACH usage context

   FOREACH is used inside PL/pgSQL to iterate over array elements one by one.

2. Step 2: Identify the main purpose

   It simplifies looping through arrays without manual index handling.

3. Final Answer:

   To loop through each element of an array easily inside PL/pgSQL. -> Option B

4. Quick Check:

   FOREACH loops over array elements [OK]

Hint: FOREACH loops over array elements inside PL/pgSQL [OK]

Common Mistakes:

• Confusing FOREACH with array creation or sorting
• Thinking FOREACH works outside PL/pgSQL
• Assuming FOREACH converts arrays to strings

2. Which of the following is the correct syntax to iterate over an integer array arr using FOREACH in PL/pgSQL?

easy

A. FOREACH element IN ARRAY arr LOOP ... END LOOP;

B. FOREACH element FROM arr LOOP ... END LOOP;

C. FOREACH element ON arr LOOP ... END LOOP;

D. FOREACH element OVER arr LOOP ... END LOOP;

Solution

1. Step 1: Recall FOREACH syntax

   The correct syntax uses 'IN ARRAY' to specify the array to loop over.

2. Step 2: Match syntax options

   Only FOREACH element IN ARRAY arr LOOP ... END LOOP; uses 'IN ARRAY' correctly; others use invalid keywords.

3. Final Answer:

   FOREACH element IN ARRAY arr LOOP ... END LOOP; -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Use 'IN ARRAY' to loop over arrays with FOREACH [OK]

Common Mistakes:

• Using FROM, ON, or OVER instead of IN ARRAY
• Omitting LOOP or END LOOP keywords
• Trying to use FOREACH outside PL/pgSQL

3. Consider the following PL/pgSQL block:

DECLARE
  arr integer[] := ARRAY[2, 4, 6];
  sum integer := 0;
BEGIN
  FOREACH val IN ARRAY arr LOOP
    sum := sum + val;
  END LOOP;
  RAISE NOTICE '%', sum;
END;

What will be the output when this block runs?

medium

A. Syntax error

B. 24

C. 12

D. 0

Solution

1. Step 1: Understand the loop iteration

   The FOREACH loops over arr elements: 2, 4, and 6.

2. Step 2: Calculate the sum

   sum starts at 0, then adds 2 + 4 + 6 = 12.

3. Final Answer:

   12 -> Option C

4. Quick Check:

   2 + 4 + 6 = 12 [OK]

Hint: Sum array elements by adding each in FOREACH loop [OK]

Common Mistakes:

• Assuming FOREACH does not add elements
• Confusing sum initialization
• Expecting syntax error due to RAISE NOTICE

4. Identify the error in this PL/pgSQL snippet:

DECLARE
  arr text[] := ARRAY['a', 'b', 'c'];
  ch text;
BEGIN
  FOREACH ch IN arr LOOP
    RAISE NOTICE '%', ch;
  END LOOP;
END;

medium

A. Variable 'ch' must be declared as integer, not text.

B. Array declaration syntax is incorrect.

C. RAISE NOTICE cannot print variables inside loops.

D. FOREACH must use 'IN ARRAY' before the array variable.

Solution

1. Step 1: Check FOREACH syntax

   FOREACH requires 'IN ARRAY' before the array variable to iterate properly.

2. Step 2: Identify the error

   The code uses 'IN arr' instead of 'IN ARRAY arr', causing syntax error.

3. Final Answer:

   FOREACH must use 'IN ARRAY' before the array variable. -> Option D

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Always write 'FOREACH var IN ARRAY arr' [OK]

Common Mistakes:

• Omitting 'ARRAY' keyword in FOREACH
• Wrong variable type for array elements
• Misunderstanding RAISE NOTICE usage

5. You want to write a PL/pgSQL function that takes an integer array and returns the count of even numbers using FOREACH. Which of the following code snippets correctly implements this?

hard

A. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

B. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val FROM arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

C. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOR val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

D. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val ON ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

Solution

1. Step 1: Check FOREACH syntax correctness

   CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$ DECLARE val integer; count integer := 0; BEGIN FOREACH val IN ARRAY arr LOOP IF val % 2 = 0 THEN count := count + 1; END IF; END LOOP; RETURN count; END; $$ LANGUAGE plpgsql; uses 'FOREACH val IN ARRAY arr LOOP', which is the correct syntax.

2. Step 2: Verify logic for counting even numbers

   Inside the loop, it checks if val is even and increments count correctly.

3. Final Answer:

   FOREACH val IN ARRAY arr LOOP with IF val % 2 = 0 logic -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... and correct IF condition [OK]

Hint: Use 'FOREACH val IN ARRAY arr' and check val % 2 = 0 [OK]

Common Mistakes:

• Using 'FROM' or 'ON ARRAY' instead of 'IN ARRAY'
• Confusing FOREACH with FOR loops
• Missing RETURN statement or wrong logic