Loops

PL/pgSQL provides several loop constructs for repeating statements. Each suits different situations.

Basic LOOP

Runs forever until an EXIT statement is reached:

DO $$
DECLARE
  v_i INTEGER := 1;
BEGIN
  LOOP
    RAISE NOTICE 'i = %', v_i;
    v_i := v_i + 1;
    EXIT WHEN v_i > 5;
  END LOOP;
END;
$$;

WHILE Loop

Checks the condition before each iteration:

DO $$
DECLARE
  v_n INTEGER := 1;
BEGIN
  WHILE v_n <= 10 LOOP
    RAISE NOTICE '%', v_n;
    v_n := v_n + 1;
  END LOOP;
END;
$$;

FOR Loop (Integer Range)

The loop variable is implicitly declared as INTEGER — do not declare it in DECLARE:

DO $$
BEGIN
  FOR i IN 1..10 LOOP
    RAISE NOTICE 'Iteration %', i;
  END LOOP;
END;
$$;

Reverse iteration:

FOR i IN REVERSE 10..1 LOOP ...

Optional step (default is 1):

FOR i IN 1..20 BY 2 LOOP ...  -- odd numbers: 1, 3, 5 …

FOR Loop (Query Results)

Iterates over each row returned by a query — the most common loop in practice:

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT first_name, salary FROM employees ORDER BY salary DESC LOOP
    RAISE NOTICE '% earns %', rec.first_name, rec.salary;
  END LOOP;
END;
$$;

The loop variable (rec) is automatically typed to match the query columns.

FOREACH Loop (Arrays)

Iterate over an array:

DO $$
DECLARE
  v_arr INTEGER[] := ARRAY[1, 2, 3, 4, 5];
  v_elem INTEGER;
BEGIN
  FOREACH v_elem IN ARRAY v_arr LOOP
    RAISE NOTICE 'Element: %', v_elem;
  END LOOP;
END;
$$;

CONTINUE

Skip the remainder of the current iteration:

FOR i IN 1..10 LOOP
  CONTINUE WHEN i % 2 = 0;  -- skip even numbers
  RAISE NOTICE '%', i;
END LOOP;

Loop Labels and Nested Loops

Labels let you EXIT or CONTINUE from an outer loop:

DO $$
BEGIN
  <<outer>>
  FOR i IN 1..3 LOOP
    FOR j IN 1..3 LOOP
      EXIT outer WHEN i = 2 AND j = 2;
      RAISE NOTICE '%, %', i, j;
    END LOOP;
  END LOOP;
END;
$$;

Choosing the Right Loop