Try a preview of AI-powered search below!

This page describes the two kinds of unbounded loop.

"Infinite loop"

The infinite loop is the most basic and most flexible form of the unbounded loop. It looks like this:

<<label_17>>loop
  <statement list 1>
  exit <label> when <boolean expression>;
  <statement list 2>
end loop label_17;

or this:

<<label_17>>loop
  <statement list 1>
  continue label_17 when <boolean expression 1>;
  <statement list 2>
  exit label_17 when <boolean expression 2>;
  <statement list 3>
end loop label_17;

An infinite loop must have either an exit statement (or, though these are rare practices, a return statement or a raise exception statement). Otherwise, it will simply iterate forever.

Try this example:

\c :db :u
drop schema if exists s cascade;
create schema s;
create table s.t(k serial primary key, v int not null);
insert into s.t(v) select generate_series(0, 99, 5);

create function s.f_infinite(k_lo in int, k_hi in int)
  returns table(k int, v int)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  cur refcursor not null := 'cur';
begin
  open cur no scroll for (
    select t.k, t.v
    from s.t
    where t.k between k_lo and k_hi
    order by t.k);

  -- Infinite loop -----------------------------------------
  loop
    fetch next from cur into k, v;
    exit when not found;
    return next;
  end loop;
  ----------------------------------------------------------

close cur;
end;
$body$;

select k, v from s.f_infinite(6, 11);

The code relies on these basic PL/pgSQL statements:

• plpgsql_open_cursor_stmt
• plpgsql_fetch_from_cursor_stmt
• plpgsql_close_cursor_stmt

See also the section Cursor manipulation in PL/pgSQL.

This is the result:

 k  | v  
----+----
  6 | 25
  7 | 30
  8 | 35
  9 | 40
 10 | 45
 11 | 50

Notice the use of the special built-in variable found. (This is described in the section The "get diagnostics" statement.)

See also the section Beware Issue #6514 at the end of the Cursors section. Because of the current restrictions that it describes, and because of the fact that fetch all is anyway not supported in PL/pgSQL in vanilla PostgreSQL, the only viable cursor operation in PL/pgSQL besides open and close is fetch next... into. Given this, the while loop approach for iterating over the results of a query shown here adds no value over what the query for loop brings.

"While loop"

The other form of the unbounded loop is the while loop. It looks like this:

<<label_42>>while <boolean expression> loop
  <statement list>
end loop label_42;

The boolean expression is evaluated before starting an iteration. If it evaluates to false, then no iteration takes place—just if the loop was written as an infinite loop and exit when not <boolean expression> were written as the very first statement inside the loop. Otherwise, the code inside the loop had better change the outcome of the boolean expression so that it eventually becomes false.

This form is sometimes referred to as the pre-tested loop because it checks the condition before executing each next iteration.

In this example, label_42 isn't mentioned in an exit statement or a continue statement. But the name used at the end of the loop statement must anyway match the name used at its start. (If they don't match, then you get the 42601 syntax error.)

As an exercise, re-write the code example from the "infinite loop section, above, to use a while loop, thus:

create function s.f_while(k_lo in int, k_hi in int)
  returns table(k int, v int)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  cur refcursor not null := 'cur';
begin
  open cur no scroll for (
    select t.k, t.v
    from s.t
    where t.k between k_lo and k_hi
    order by t.k);
    
  -- While loop --------------------------------------------
  fetch next from cur into k, v;
  while found loop
    return next;
    fetch next from cur into k, v;
  end loop;
  ----------------------------------------------------------
  
  close cur;
end;
$body$;

select k, v from s.f_while(6, 11);

It produces exactly the same result as does s.f_infinite(). Notice that the infinite loop and the while loop each uses the same number, five, of code lines. Stylists debate which version is nicer—and the choice is determined by taste. Very often, the while loop requires some code before the loop to establish the starting condition. And in this case, fetch next from cur into k, v is written twice: both before the loop and inside it. In contrast, with the infinite loop, it's written just once. The code inside the while loop ("print the result from the previous iteration and then get the next result") feels back-to-front in comparison with the infinite loop's "get the next result and print it". Sometimes, depending on the use case, the while loop feels like the better choice.

Notice that this is legal:

<<strange>>while true loop
  <statement list>
  continue <label> when <boolean expression>;
  <statement list>
  exit <label> when <boolean expression>;
  <statement list>
end loop strange;

However, the effect of writing "while true loop" is indistinguishable from the effect of writing just "loop". Using the verbose form is therefore pointless; and it's likely that doing so will simply confuse the reader.