LOOP, WHILE, FOR iterations in PostgreSQL

Introduction

Loops help you repeat actions many times without writing the same code again and again.

When you want to process each row in a table one by one.

When you need to repeat a task until a condition is met, like counting or checking values.

When you want to generate a series of numbers or dates automatically.

When you want to perform batch updates or inserts step by step.

When you want to automate repetitive database tasks inside a function.

Syntax

PostgreSQL

LOOP
  -- statements
END LOOP;

WHILE condition LOOP
  -- statements
END LOOP;

FOR record_variable IN query LOOP
  -- statements
END LOOP;

LOOP repeats forever until you use EXIT to stop it.

WHILE repeats as long as the condition is true.

FOR loops over each row returned by a query.

Examples

A simple LOOP that prints 'Hello!' once and then stops.

PostgreSQL

LOOP
  RAISE NOTICE 'Hello!';
  EXIT;
END LOOP;

A WHILE loop that counts from 1 to 3 and prints each number.

PostgreSQL

DO $$
DECLARE
  counter INT := 1;
BEGIN
  WHILE counter <= 3 LOOP
    RAISE NOTICE 'Count: %', counter;
    counter := counter + 1;
  END LOOP;
END $$;

A FOR loop that goes through each user and prints their id and name.

PostgreSQL

FOR rec IN SELECT id, name FROM users LOOP
  RAISE NOTICE 'User: % - %', rec.id, rec.name;
END LOOP;

Sample Program

This program uses a WHILE loop to print numbers from 1 to 5.

PostgreSQL

DO $$
DECLARE
  counter INT := 1;
BEGIN
  WHILE counter <= 5 LOOP
    RAISE NOTICE 'Number: %', counter;
    counter := counter + 1;
  END LOOP;
END $$;

Important Notes

Remember to use EXIT inside LOOP to avoid infinite loops.

Use RAISE NOTICE to print messages during loop execution.

FOR loops are great for processing query results row by row.

Summary

LOOP repeats code until you tell it to stop with EXIT.

WHILE repeats as long as a condition is true.

FOR loops go through each row from a query automatically.