Introduction to PL/pgSQL Loop statement

Theloop defines an unconditional loop that executes a block of code repeatedly until terminated by anexit orreturn statement.

The following illustrates the syntax of theloop statement:

<<label>>loop   statements;end loop;

Typically, you use anif statement to terminate the loop based on a condition like this:

<<label>>loop   statements;   if conditionthen      exit;   end if;end loop;

Theexit statement terminates the loop immediately.

It’s possible to place a loop statement inside another loop statement. When aloop statement is placed inside anotherloop statement, it is called a nested loop:

<<outer>>loop   statements;   <<inner>>   loop     /* ... */     exit<<inner>>   end loop;end loop;

When you have nested loops, it’s necessary to use loop labels. The loop labels allow you to specify the loop in theexit andcontinue statements, indicating which loop these statements refer to.

PL/pgSQL loop statement examples

Let’s explore some examples of using the loop statement.

1) Basic PL/pgSQL loop example

The following example uses aloop statement to display five numbers from 1 to five:

do $$declare    counter int := 0;begin  loop  counter = counter + 1;raise notice'%',counter;if counter = 5 thenexit;end if;  end loop;end;$$;

Output:

NOTICE:  1NOTICE:  2NOTICE:  3NOTICE:  4NOTICE:  5

How it works.

First, declare a variablecounter and initialize its value to zero:

counterint := 0;

Second, increase the value of thecounter variable by one in each iteration of the loop:

counter = counter + 1;

Third, display the current value of thecounter:

raise notice '%', counter;

Finally, terminate the loop if the current value of thecounter variable is 5:

if counter = 5 then   exit;end if;

Since the initial value of thecounter is zero, the loop executes five times before it is terminated.

In practice, you can combine theif andexit statements into a single statement like this:

exit when counter= 5;

For example:

do $$declare    counter int := 0;begin  loop  counter = counter + 1;raise notice'%',counter;exitwhen counter = 5;  end loop;end;$$;

Note that you will learn more about theexit statement in the upcoming tutorial.

2) Using a loop with a label

The following example illustrates how to use a loop label:

do $$declare    counter int := 0;begin <<my_loop>>  loop  counter = counter + 1;raise notice'%',counter;exit my_loopwhen counter = 5;  end loop;end;$$;

Output:

NOTICE:  1NOTICE:  2NOTICE:  3NOTICE:  4NOTICE:  5

How it works.

In this example, we place a loop labelmy_loop inside<<>> before theLOOP keyword.

Inside the loop’s body, we reference the loop label (my_loop) in theexit statement to explicitly instruct PostgreSQL to terminate the loop specified by the loop label:my_loop.

It’ll be more practical to use a loop label when you have a nested loop.

3) Nested loop example

The following example illustrates how to use a nested loop with labels:

do $$declarerow_varint := 0;col_varint := 0;begin<<outer_loop>>looprow_var= row_var+ 1;<<inner_loop>>loopcol_var= col_var+ 1;raise notice'(%, %)', row_var, col_var;-- terminate the inner loopexit inner_loopwhen col_var= 3;end loop;-- reset the columncol_var= 0;-- terminate the outer loopexit outer_loopwhen row_var= 3;end loop;end;$$;

Output:

NOTICE:  (1, 1)NOTICE:  (1, 2)NOTICE:  (1, 3)NOTICE:  (2, 1)NOTICE:  (2, 2)NOTICE:  (2, 3)NOTICE:  (3, 1)NOTICE:  (3, 2)NOTICE:  (3, 3)

How it works.

First, declare two variablesrow_var andcol_var, and initialize their values to zero:

row_var int := 0;col_var int := 0;

Second, use theouter_loop as the label for the outer loop. In the outer loop, increase the value of therow_var by one, execute the nested loop, and reset thecol_var in each iteration.

If therow_var is 3, exit the outer loop by referencing theouter_loop label in theexit statement.

Third, use theinner_loop as the label for the inner loop. In the inner loop, increase the value ofcol_var by one, display the current values ofrow_var andcol_var variables, and terminate the inner loop when the value ofcol_var is 3.

Summary

• Use the PL/pgSQLLOOP statement to create unconditional loops.
• The loop can be nested.
• Use theexit statement to terminate a loop prematurely.

Last updated onMarch 19, 2024

On this page

• Introduction to PL/pgSQL Loop statement
• PL/pgSQL loop statement examples
• Summary