FOREACH for array iteration in PostgreSQL - Step-by-Step Execution

Concept Flow - FOREACH for array iteration

Start FOREACH loop

↓

Pick next element from array

↓

Execute loop body with element

↓

More elements?

Yes→Pick next element

No↓

End loop

The FOREACH loop picks each element from the array one by one and runs the loop body with that element until all elements are processed.

Execution Sample

PostgreSQL

DO $$
DECLARE
  arr integer[] := ARRAY[10, 20, 30];
  val integer;
BEGIN
  FOREACH val IN ARRAY arr LOOP
    RAISE NOTICE 'Value: %', val;
  END LOOP;
END $$;

This code loops through each number in the array and prints it.

Execution Table

Step	Current Element (val)	Action	Output
1	10	Start loop, pick first element	Value: 10
2	20	Pick next element	Value: 20
3	30	Pick next element	Value: 30
4	N/A	No more elements, exit loop	Loop ends

💡 All elements in the array have been processed, so the loop ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
val	NULL	10	20	30	30

Key Moments - 3 Insights

What is the value of 'val' after the loop ends?

30. It retains the value of the last element assigned during the loop (see variable_tracker 'Final' column).

What happens if the array is empty?

The loop body never runs because there are no elements to pick, so no output is produced.

Can FOREACH be used with arrays of types other than integers?

Yes, FOREACH works with any array type, like text[], boolean[], etc., as long as the loop variable matches the element type.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'val' at step 2?

A 10

B 20

C 30

D NULL

💡 Hint

Check the 'Current Element (val)' column at step 2 in the execution_table.

At which step does the FOREACH loop end?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look for the row where the action says 'No more elements, exit loop' in the execution_table.

If the array was empty, what would the output be?

A No output

B Value: NULL

C Error

D Value: 0

💡 Hint

Refer to the key_moments section about empty arrays and loop execution.

Concept Snapshot

FOREACH loops through each element in an array.
Syntax: FOREACH var IN ARRAY array_var LOOP ... END LOOP;
Each iteration assigns the next element to var.
Loop ends when all elements are processed.
Works with any array type matching var's type.

Full Transcript

The FOREACH loop in PostgreSQL lets you run code for each element in an array. It picks elements one by one, assigns them to a variable, and runs the loop body. When no elements remain, the loop stops. If the array is empty, the loop body never runs. This works for arrays of any type, as long as the loop variable matches the element type. The example code loops through an integer array and prints each number. The variable holding the element changes each step and retains the last value after the loop ends.