FOREACH for array iteration in PostgreSQL

Introduction

FOREACH helps you look at each item in a list one by one inside a database function. It makes working with lists easy without writing complex loops.

When you want to process each item in a list stored in a database array.

When you need to perform an action for every element in an array inside a PostgreSQL function.

When you want to check or modify each value in an array one by one.

When you want to insert or update rows based on each element of an array.

When you want to avoid writing manual loops and use a simple way to handle arrays.

Syntax

PostgreSQL

FOREACH element_variable IN ARRAY array_variable LOOP
    -- statements using element_variable
END LOOP;

The element_variable holds the current item from the array during each loop.

The array_variable is the array you want to go through.

Examples

This example prints each number from the array one by one.

PostgreSQL

DECLARE
    current_number integer;
    numbers integer[] := ARRAY[1, 2, 3];
BEGIN
    FOREACH current_number IN ARRAY numbers LOOP
        RAISE NOTICE 'Number: %', current_number;
    END LOOP;
END;

This example goes through a text array and prints each fruit.

PostgreSQL

DECLARE
    current_text text;
    texts text[] := ARRAY['apple', 'banana', 'cherry'];
BEGIN
    FOREACH current_text IN ARRAY texts LOOP
        RAISE NOTICE 'Fruit: %', current_text;
    END LOOP;
END;

If the array is empty, the loop does not run at all.

PostgreSQL

DECLARE
    current_item integer;
    empty_array integer[] := ARRAY[]::integer[];
BEGIN
    FOREACH current_item IN ARRAY empty_array LOOP
        RAISE NOTICE 'This will not print';
    END LOOP;
END;

Sample Program

This program shows the array and then prints each value inside it using FOREACH.

PostgreSQL

DO $$
DECLARE
    current_value integer;
    sample_array integer[] := ARRAY[10, 20, 30, 40];
BEGIN
    RAISE NOTICE 'Array before loop: %', sample_array;
    FOREACH current_value IN ARRAY sample_array LOOP
        RAISE NOTICE 'Current value: %', current_value;
    END LOOP;
END $$;

Important Notes

FOREACH runs in O(n) time where n is the number of elements in the array.

It uses constant extra space, just one variable to hold the current element.

A common mistake is forgetting to declare the element variable with the correct type.

Use FOREACH when you want simple, readable code to process arrays instead of manual index loops.

Summary

FOREACH lets you easily go through each item in a PostgreSQL array.

It simplifies looping by handling the array traversal for you.

Use it inside PL/pgSQL functions to work with array elements one by one.