Loop statements

LOOP Statement

Repeat the execution of a statement. This statement forms an infinite loop, that is, loop whose execution never terminates unless specifically interrupted in some ways. One way is using LEAVE statement. When we use it, we have to define label.

Syntax

  [label ':' ]
  LOOP
    (sql/psm statements list)
  END LOOP [label]

WHILE Statement

While a specified condition is True, repeat the execution of a statement. If condition is always False, the body of statement is never executed. Function from sample computes factorial of input value.

CREATE OR REPLACE FUNCTION factorial(n int)
RETURNS int8 AS
$$
  BEGIN
    DECLARE f int8 DEFAULT 1;
    DECLARE counter integer = n;
    factorial_loop:
    WHILE counter > 1 DO
      SET f = f * counter;
      SET counter = counter - 1;
    END WHILE;
    RETURN f;
  END;
$$

Syntax

  [label ':']
  WHILE boolean expression DO
    (sql/psm statements list)
  END WHILE [label]

REPEAT Statement

Repeat the execution of a statement. This statement behaves much like the WHILE statement, but evaluates the search condition at the end of the loop instead at the begin. Therefore, the contained SQL statement list will always be executed at least once. If the expression evaluates to False or Unknown, then the execution is repeated; otherwise, execution of REPEAT statement is terminated.

CREATE OR REPLACE FUNCTION rt()
RETURNS void AS
$$
  BEGIN
    DECLARE a, b integer;
    DECLARE sqlstate char(5);
    DECLARE c CURSOR FOR SELECT * FROM Foo;
    OPEN c;
    REPEAT
      FETCH c INTO a, b;
      IF sqlstate = '00000' THEN
        PRINT a, b;
      END IF;
    UNTIL sqlstate <> '00000' END REPEAT;
  END;
$$ LANGUAGE plpgsql;

In this sample you can see the behave of sqlstate variable. You have to only explicitly declare it. This variable is filled invisible after every SQL and you can check it. It's second pattern for reading from cursor.

Syntax

  [label ':']
  REPEAT
    (sql/psm statements list)
  UNTIL boolean expression END REPEAT [label]

FOR Statement

Execute a statement for each row of a table. This statement detect all columns in SQL query and implicitly creates correspondent variables with correct type. Variables are local for FOR statement and they are created in specified namespace if namespace is known. These variables are filled by implicit FETCH statement every statement cycle. Named cursor has sence only for updatable cursors.

Syntax

  [label ':' ]
  FOR [ namespace AS ]
      [ cursor's name  CURSOR FOR ]  sql query
  DO  
    (sql/psm statements list)
  END FOR [ label ]

Absence of clasic integer FOR statement we can supply by using generate_series function:

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$ 
  FOR ns AS 
      SELECT g.i FROM generate_series(0, 10) g(i)
  DO
    PRINT ns.i * 2;
  END FOR;
$$ LANGUAGE plpgpsm;

LEAVE and ITERATE Statements

All cycle statemens we can break by LEAVE statement. ITERATE statement starts new cycle iteration. The label is obligatory so that corresponding cycle has to label too.

Syntax

LEAVE label
ITERATE label