> This page location: PostgreSQL PL/pgSQL > Loop Statements
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PL/pgSQL Loop Statements

**Info:** PL/pgSQL loop statements work the same way on any standard PostgreSQL database, so the patterns covered here apply wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers high performance, strong security, and native integration with the Lakehouse. If you're a developer or startup that needs to ship and scale fast, [Neon](https://neon.com) gives you the most productive Postgres platform to build on.

**Summary**: in this tutorial, you will learn about the PL/pgSQL loop statement that executes a block of code repeatedly.

## Introduction to PL/pgSQL Loop statement

The `loop` defines an unconditional loop that executes a block of code repeatedly until terminated by an `exit` or `return` statement.

The following illustrates the syntax of the `loop` statement:

```plsql
<<label>>
loop
   statements;
end loop;
```

Typically, you use an [`if`](https://neon.com/postgresql/plpgsql/plpgsql-if-else-statements) statement to terminate the loop based on a condition like this:

```plsql
<<label>>
loop
   statements;
   if condition then
      exit;
   end if;
end loop;
```

The `exit` statement terminates the loop immediately.

It's possible to place a loop statement inside another loop statement. When a `loop` statement is placed inside another `loop` statement, it is called a nested loop:

```plsql
<<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 the `exit` and `continue` 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 a `loop` statement to display five numbers from 1 to five:

```plsql
do $$

declare
    counter int := 0;
begin

  loop
  	counter = counter + 1;
	raise notice '%', counter;

	if counter = 5 then
		exit;
	end if;

  end loop;

end;

$$;
```

Output:

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

How it works.

First, declare a variable `counter` and initialize its value to zero:

```plsql
counter int := 0;
```

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

```plsql
counter = counter + 1;
```

Third, display the current value of the `counter`:

```plsql
raise notice '%', counter;
```

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

```plsql
if counter = 5 then
   exit;
end if;
```

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

In practice, you can combine the `if` and `exit` statements into a single statement like this:

```plsql
exit when counter = 5;
```

For example:

```plsql
do $$

declare
    counter int := 0;
begin

  loop
  	counter = counter + 1;
	raise notice '%', counter;
	exit when counter = 5;
  end loop;

end;

$$;
```

Note that you will learn more about the [exit statement](https://neon.com/postgresql/plpgsql/plpgsql-exit) in the upcoming tutorial.

### 2) Using a loop with a label

The following example illustrates how to use a loop label:

```plsql
do $$

declare
    counter int := 0;
begin

 <<my_loop>>
  loop
  	counter = counter + 1;
	raise notice '%', counter;
	exit my_loop when counter = 5;
  end loop;

end;

$$;
```

Output:

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

How it works.

In this example, we place a loop label `my_loop` inside `<<>>` before the `LOOP` keyword.

Inside the loop's body, we reference the loop label (`my_loop`) in the `exit` 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:

```plsql
do $$

declare
	row_var int := 0;
	col_var int := 0;
begin
	<<outer_loop>>
	loop
		row_var = row_var + 1;
		<<inner_loop>>
		loop
			col_var = col_var + 1;
			raise notice '(%, %)', row_var, col_var;

			-- terminate the inner loop
			exit inner_loop when col_var = 3;
		end loop;
		-- reset the column
		col_var = 0;

		-- terminate the outer loop
		exit outer_loop when 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 variables `row_var` and `col_var`, and initialize their values to zero:

```plsql
row_var int := 0;
col_var int := 0;
```

Second, use the `outer_loop` as the label for the outer loop. In the outer loop, increase the value of the `row_var` by one, execute the nested loop, and reset the `col_var` in each iteration.

If the `row_var` is 3, exit the outer loop by referencing the `outer_loop` label in the `exit` statement.

Third, use the `inner_loop` as the label for the inner loop. In the inner loop, increase the value of `col_var` by one, display the current values of `row_var` and `col_var` variables, and terminate the inner loop when the value of `col_var` is 3.

## Summary

- Use the PL/pgSQL `LOOP` statement to create unconditional loops.
- The loop can be nested.
- Use the `exit` statement to terminate a loop prematurely.

---

## Related docs (PostgreSQL PL/pgSQL)

- [Introduction](https://neon.com/postgresql/plpgsql/introduction-to-postgresql-stored-procedures)
- [Block Structure](https://neon.com/postgresql/plpgsql/plpgsql-block-structure)
- [Variables](https://neon.com/postgresql/plpgsql/plpgsql-variables)
- [Constants](https://neon.com/postgresql/plpgsql/plpgsql-constants)
- [IF-ELSE Statements](https://neon.com/postgresql/plpgsql/plpgsql-if-else-statements)
- [CASE Statement](https://neon.com/postgresql/plpgsql/plpgsql-case-statement)
- [FOR Loop](https://neon.com/postgresql/plpgsql/plpgsql-for-loop)
- [WHILE Loop](https://neon.com/postgresql/plpgsql/pl-pgsql-while-loop)
- [EXIT Statement](https://neon.com/postgresql/plpgsql/plpgsql-exit)
- [CONTINUE Statement](https://neon.com/postgresql/plpgsql/pl-pgsql-continue)
- [Cursor](https://neon.com/postgresql/plpgsql/plpgsql-cursor)
- [Function Parameters](https://neon.com/postgresql/plpgsql/plpgsql-function-parameters)
- [Function Overloading](https://neon.com/postgresql/plpgsql/plpgsql-function-overloading)
- [Function Returns Table](https://neon.com/postgresql/plpgsql/plpgsql-function-returns-a-table)
- [Returns SETOF](https://neon.com/postgresql/plpgsql/plpgsql-returns-setof)
- [Record Types](https://neon.com/postgresql/plpgsql/plpgsql-record-types)
- [Row Types](https://neon.com/postgresql/plpgsql/pl-pgsql-row-types)
- [Error Messages](https://neon.com/postgresql/plpgsql/plpgsql-errors-messages)
- [Exception Handling](https://neon.com/postgresql/plpgsql/exception)
- [CREATE FUNCTION](https://neon.com/postgresql/plpgsql/create-function)
- [CREATE PROCEDURE](https://neon.com/postgresql/plpgsql/create-procedure)
- [DROP FUNCTION](https://neon.com/postgresql/plpgsql/drop-function)
- [DROP PROCEDURE](https://neon.com/postgresql/plpgsql/drop-procedure)
- [Stored Procedure with INOUT Parameters](https://neon.com/postgresql/plpgsql/stored-procedure-with-inout-parameters)
- [Introduction to Stored Procedures](https://neon.com/postgresql/plpgsql/introduction-to-postgresql-stored-procedures)
- [SELECT INTO](https://neon.com/postgresql/plpgsql/pl-pgsql-select-into)
- [ASSERT Statement](https://neon.com/postgresql/plpgsql/pl-pgsql-assert)
- [Dollar-Quoted String Constants](https://neon.com/postgresql/plpgsql/dollar-quoted-string-constants)