Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Syntax of PL/pgSQL
Function parameters
Handling exceptions
Summary

Instruction

The next type of loop widely used in Postgres is the FOR loop.

A FOR loop iterates a specified number of times. The lower and upper bound of the range are evaluated once when entering the loop.

Below are some examples of integer FOR loops:

FOR i IN 1..10 LOOP
  /* i will take on the values:
  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
  within the loop */
END LOOP;

If the REVERSE keyword is specified, then the step value is subtracted, rather than added, after each iteration.

FOR i IN REVERSE 10..1 LOOP
  /* i will take on the values:
  10, 9, 8, 7, 6, 5, 4, 3, 2, 1
  within the loop */
END LOOP;

The iteration step is 1 by default or the value (if any) specified in the BY clause.

FOR i IN REVERSE 10..1 BY 2 LOOP
  /* i will take on the values:
  10, 8, 6, 4, 2
  within the loop */
END LOOP;

Exercise

Create a function named print_odd_numbers() that prints (by raising notices) all odd integers between 20 and 1.

The final output should look like this:

Odd Number: 19
Odd Number: 17
Odd Number: 15
Odd Number: 13
Odd Number: 11
Odd Number: 9
Odd Number: 7
Odd Number: 5
Odd Number: 3
Odd Number: 1

Stuck? Here's a hint!

You may go either way:

  1. Use FOR ... LOOP with the step (BY 2).
  2. Use FOR ... LOOP with the IF statement and a modulo operator (counter % 2) to determine if a number is odd or even.

Then, simply use the following:

RAISE NOTICE 'Odd Number: %', counter;