PL/pgSQL provides two forms of the FOR statement, and they are used to:

• Iterate through the rows returned by an SQL query
• Iterate through a range of integer values

The syntax of the FOR loop statement is:

[ <<label>> ]
FOR name IN [ REVERSE ] expression1 .. expression2 [ BY expression ] LOOP
    statements
END LOOP [ label ];

The name is the name of a local variable of the type integer. This local variable scope is the FOR loop. Statements inside the loop can read this variable, but cannot change its value. Finally, one can change this behavior by defining the variable in the declaration section of the outer block. expression1 and expression2 must be evaluated to integer values; if expression1 equals expression2 then the FOR loop is run only once.

The REVERSE keyword is optional, and it is used to define the order in which the range will be generated (ascending or descending). If REVERSE is omitted, then expression1 should be smaller than expression2, otherwise the loop will not be executed. Finally, BY defines the steps between two successive numbers in the range. Note that the BY expression value should always be a positive integer. The following example shows a FOR loop iterating over a negative range of numbers in the reverse order, the following example will print the values -1, -3,...,-9.

DO $$
BEGIN
  FOR j IN REVERSE -1 .. -10 BY 2 LOOP
      Raise notice '%', j;
  END LOOP;
END; $$ LANGUAGE plpgsql;

To iterate through the result of a set query, the syntax is different, as follows:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

target is a local variable in the outer block. Its type is not restricted to simple types such as integer and text. However, its type might be a composite or a RECORD data type. In PL/pgSQL, one could iterate over a CURSOR result or over a SELECT statement result. Cursor is a special data object that can be used to encapsulate a SELECT query, and then to read the query result a few rows at a time. The following example shows all the database names:

DO $$
DECLARE
  database RECORD;
BEGIN
  FOR database IN SELECT * FROM pg_database LOOP
    RAISE notice '%', database.datname;
  END LOOP;
END; $$;
----- output 
NOTICE: postgres
NOTICE: template1
NOTICE: template0
....
DO