Summary: in this tutorial, we will introduce you to various kinds of PL/pgSQL loop statements that execute a block of statements repeatedly.
PostgreSQL provides you with three loop statements: LOOP, WHILE loop, and FOR loop.
PL/pgSQL LOOP statement
Sometimes, you need to execute a block of statements repeatedly until a condition becomes true. To do this, you use the PL/pgSQL LOOP statement. The following illustrates the syntax of the LOOP statement:
1 2 3 4 5 | <<label>> LOOP Statements; EXIT [<<label>>] WHEN condition; END LOOP; |
The LOOP statement is also referred to as an unconditional loop statement because it executes the statements until the condition in the EXIT statement evaluates to true. Note that the condition specified after the WHEN keyword in the EXIT statement is a Boolean expression that evaluates to true or false.
Loop statements can be nested. A LOOP statement is placed inside another LOOP statement is known as a nested loop. In this case, you need to the loop label to specify explicitly which loop you want to terminate in the EXIT statement.
PL/pgSQL LOOP example
In this example, we will use the LOOP statement to develop a function that returns the nth Fibonacci sequence number.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE OR REPLACE FUNCTION fibonacci (n INTEGER) RETURNS INTEGER AS $$ DECLARE counter INTEGER := 0 ; i INTEGER := 0 ; j INTEGER := 1 ; BEGIN IF (n < 1) THEN RETURN 0 ; END IF; LOOP EXIT WHEN counter = n ; counter := counter + 1 ; SELECT j, i + j INTO i, j ; END LOOP ; RETURN i ; END ; $$ LANGUAGE plpgsql; |
The Fibonacci function accepts an integer and returns the nth Fibonacci number. By definition, Fibonacci numbers are sequence of integers starting with 0 and 1, and each subsequent number is the product the previous two numbers, for example 1, 1, 2 (1+1), 3 (2+1), 5 (3 +2), 8 (5+3), …
In the declaration section, the counter variable is initialized to zero (0). Inside the loop, when counter equals n, the loop exits. The statement:
1 | SELECT j, i + j INTO i, j ; |
swaps i and j at the same time without using a temporary variable.
PL/pgSQL WHILE loop
The WHILE loop statement executes a block of statements until a condition evaluates to false. In the WHILE loop statement, PostgreSQL evaluates the condition before executing the block of statements. If the condition is true, the block of statements is executed until it is evaluated to false.
The following flowchart illustrates the WHILE loop statement.
The following is the syntax of the WHILE loop statement.
1 2 3 4 | [ <<label>> ] WHILE condition LOOP statements; END LOOP; |
PL/pgSQL WHILE loop example
We can use the WHILE loop statement to rewrite the Fibonacci function in the first example as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE FUNCTION fibonacci (n INTEGER) RETURNS INTEGER AS $$ DECLARE counter INTEGER := 0 ; i INTEGER := 0 ; j INTEGER := 1 ; BEGIN IF (n < 1) THEN RETURN 0 ; END IF; WHILE counter <= n LOOP counter := counter + 1 ; SELECT j, i + j INTO i, j ; END LOOP ; RETURN i ; END ; |
PL/pgSQL FOR loop statement
The FOR loop statement is the most complex loop statement in PostgreSQL. We will examine each form of the FOR loop statement in detail.
FOR loop for looping through a ranges of integers
The following illustrates the syntax of the FOR loop statement that loops through a ranges of integers:
1 2 3 4 | [ <<label>> ] FOR loop_counter IN [ REVERSE ] from.. to [ BY expression ] LOOP statements END LOOP [ label ]; |
First, PostgreSQL creates a integer variable loop_counter that exists only inside the loop. By default, the loop counter is added after each iteration, If you use the REVERSE keyword, PostgreSQL will subtract the loop counter.
Second, the from and to are expressions that specify the lower and upper bound of the range. PostgreSQL evaluates those expressions before entering the loop.
Third, the expression following the BY clause specifies the iteration step. If you omit this, the default step is 1. PostgreSQL also evaluates this expression once on loop entry.
The following flowchart illustrates the FOR loop statement:
The following are some examples of using FOR loop statement.
Loop through 1 to 5 and print out a message in each iteration. The counter takes 1, 2, 3, 4, 5. In each loop iteration, PostgreSQL adds 1 to the counter.
1 2 3 4 5 6 | DO $$ BEGIN FOR counter IN 1..5 LOOP RAISE NOTICE 'Counter: %', counter; END LOOP; END; $$ |
1 2 3 4 5 | NOTICE: Counter: 1 NOTICE: Counter: 2 NOTICE: Counter: 3 NOTICE: Counter: 4 NOTICE: Counter: 5 |
Loop through 5 to 1 and print a message in each iteration. The counter takes 5, 4, 3, 2, 1. In each iteration, PostgreSQL subtracts 1 from the counter.
1 2 3 4 5 6 | DO $$ BEGIN FOR counter IN REVERSE 5..1 LOOP RAISE NOTICE 'Counter: %', counter; END LOOP; END; $$ |
1 2 3 4 5 | NOTICE: Counter: 5 NOTICE: Counter: 4 NOTICE: Counter: 3 NOTICE: Counter: 2 NOTICE: Counter: 1 |
Loop through 1 to 6, and print out the counter in each loop iteration. The counter takes 1, 3, 5. In each iteration, PostgreSQL adds 2 to the counter.
1 2 3 4 5 6 | DO $$ BEGIN FOR counter IN 1..6 BY 2 LOOP RAISE NOTICE 'Counter: %', counter; END LOOP; END; $$ |
1 2 3 | NOTICE: Counter 1 NOTICE: Counter 3 NOTICE: Counter 5 |
FOR loop for looping through a query result
You can use the FOR loop statement to loop through a query result. The syntax is as below:
1 2 3 4 | [ <<label>> ] FOR target IN query LOOP statements END LOOP [ label ]; |
The following function accepts an integer which specifies the number of rows to query from the film table in the sample database. The FOR loop statement loops through rows returned from the query and print out the film title.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE OR REPLACE FUNCTION for_loop_through_query( n INTEGER DEFAULT 10 ) RETURNS VOID AS $$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT title FROM film ORDER BY title LIMIT n LOOP RAISE NOTICE '%', rec.title; END LOOP; END; $$ LANGUAGE plpgsql; |
1 | SELECT for_loop_through_query(5); |
1 2 3 4 5 | NOTICE: Academy Dinosaur NOTICE: Ace Goldfinger NOTICE: Adaptation Holes NOTICE: Affair Prejudice NOTICE: African Egg |
FOR loop for looping through a query result of a dynamic query
Sometimes, you want to construct a dynamic query within PL/pgSQL function and loop through its result. To do so, you use the following syntax of the FOR loop statement.
1 2 3 4 5 | [ <<label>> ] FOR row IN EXECUTE string_expression [ USING query_param [, ... ] ] LOOP statements END LOOP [ label ]; |
Instead of a SQL statement, you use a string expression that is a SQL statement in text format. This allows you to construct the query dynamically.
In case the query has parameters, you use the USING statement to pass the parameter to the query.
The following function demonstrates how to use the FOR loop statement to loop through a dynamic query. It accepts two parameters:
- sort_type: 1 means sort the query result by title, 2 means sort the result by release year.
- n: the number of rows to query from the
filmtable. Notice that it will be used in theUSINGclause.
First, we build the query based on the input parameters and then we execute the query inside the FOR loop function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE OR REPLACE FUNCTION for_loop_through_dyn_query( sort_type INTEGER, n INTEGER ) RETURNS VOID AS $$ DECLARE rec RECORD; query text; BEGIN query := 'SELECT title, release_year FROM film '; IF sort_type = 1 THEN query := query || 'ORDER BY title'; ELSIF sort_type = 2 THEN query := query || 'ORDER BY release_year'; ELSE RAISE EXCEPTION 'Invalid sort type %s', sort_type; END IF; query := query || ' LIMIT $1'; FOR rec IN EXECUTE query USING n LOOP RAISE NOTICE '% - %', rec.release_year, rec.title; END LOOP; END; $$ LANGUAGE plpgsql; |
The following statement calls for_loop_through_dyn_query() function that get 5 films and sort them by title:
1 | SELECT for_loop_through_dyn_query(1,5); |
1 2 3 4 5 | NOTICE: 2006 - Academy Dinosaur NOTICE: 2006 - Ace Goldfinger NOTICE: 2006 - Adaptation Holes NOTICE: 2006 - Affair Prejudice NOTICE: 2006 - African Egg |
The following statement calls for_loop_through_dyn_query() function that get 5 films and sort them by release year:
1 | SELECT for_loop_through_dyn_query(2,5); |
1 2 3 4 5 | NOTICE: 2006 - Grosse Wonderful NOTICE: 2006 - Airport Pollock NOTICE: 2006 - Bright Encounters NOTICE: 2006 - Academy Dinosaur NOTICE: 2006 - Chamber Italian |
In this tutorial, we have shown you how to use the PL/pgSQL loop statement that executes a block of statement repeatedly.