Iterative Control in PL/pgSQL

LOOP Statements

• Loops repeat a statement or sequence of statements multiple times.
• There are three loop types:
• Basic loop
• FOR loop
• WHILE loop

Basic Loops

• Syntax :

          LOOP

          statement1;

          . . .

          EXIT [WHEN condition];

          END LOOP;

• Example :

         create or replace function f2() returns void as

         $$

         DECLARE

         ctr numeric:=0;

         BEGIN

               LOOP

                     raise notice '%',ctr;

                     ctr:=ctr+1;

                     EXIT WHEN ctr > 3;

               END LOOP;

         END;

$$ language plpgsql;

WHILE Loops

• Syntax :

          WHILE condition LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Use the WHILE loop to repeat statements 
• while a condition is TRUE.

FOR Loops

• Use a FOR loop to shortcut the test for the numeric of iterations.
• Do not declare the counter; it is declared implicitly.
• 'lower_bound .. upper_bound' is required syntax.

          FOR counter IN [REVERSE]

          lower_bound..upper_bound LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Example :

          create or replace function f3() returns void as

          $$

          BEGIN

          FOR ctr IN 1..3 LOOP

          raise notice '%',ctr;

          END LOOP;

          END;

          $$ language plpgsql;

Loop through Query Results

FOR record_or_row IN query LOOP

statements

END LOOP [ label ];

create or replace function f4() returns void as

$$

DECLARE

rec emp%ROWTYPE;

BEGIN

FOR rec IN select * from emp LOOP

raise notice '%',rec.ename;

END LOOP;

END;

$$ language plpgsql;