Learning PostgreSQL 11 - Third Edition

Loop statements

The basic LOOP statement has the following structure:

[ <<label>> ]LOOP    statementsEND LOOP [ label ];

To understand the LOOP statement, let's rewrite the factorial function, as follows:

DROP FUNCTION IF EXISTS factorial (int);CREATE OR REPLACE FUNCTION factorial (fact int) RETURNS BIGINT AS $$DECLARE  result bigint = 1;BEGIN  IF fact = 1 THEN RETURN 1;  ELSIF fact IS NULL or fact < 1 THEN RAISE EXCEPTION 'Provide a positive integer';  ELSE    LOOP      result = result*fact;      fact = fact-1;      EXIT WHEN fact = 1;    END Loop;  END IF;  RETURN result;END; $$ LANGUAGE plpgsql;

In the preceding code, the conditional EXIT statement is used to prevent infinite looping by exiting the LOOP statement. When an EXIT statement is encountered, the execution ...