PL/PGSQL case and Looping statements

PL/pgSQL CASE Statement:

Syntax:

CASE search-expression
WHEN expression_1 [, expression_2, …] THEN
when-statements
[ … ]
[ELSE
else-statements ]
END CASE;

Example:

SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

PL/pgSQL Loop Statements:

• LOOP
• WHILE loop
• FOR loop

PL/pgSQL Loop:

The LOOP statement is also referred to as an unconditional loop statement because it executes the statements until the condition in the EXIT statement evaluates to true. Note that the condition specified after the WHEN keyword in the EXIT statement is a Boolean expression that evaluates to true or false.
Loop statements can be nested. A LOOP statement is placed inside another LOOP statement is known as a nested loop. In this case, you need to the loop label to specify explicitly which loop you want to terminate in the EXIT statement.

Syntax:

<>
LOOP
Statements;
EXIT [<>] WHEN condition;
END LOOP;

Example:

CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
a INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
LOOP
EXIT WHEN a = n ;
a := a + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
$$ LANGUAGE plpgsql;

PL/pgSQL WHILE Loop:

The WHILE loop statement executes a block of statements until a condition evaluates to false. In the WHILE loop statement, PostgreSQL evaluates the condition before executing the block of statements. If the condition is true, the block of statements is executed until it is evaluated to false.
The following flowchart illustrates the WHILE loop statement.

Syntax:

[ <> ]
WHILE condition LOOP
statements;
END LOOP;

Example:

CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
WHILE counter <= n LOOP
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;

PL/pgSQL FOR loop:

The FOR loop statement is the most complex loop statement in PostgreSQL.

Syntax:

[ <> ]
FOR loop_counter IN [ REVERSE ] from.. to [ BY expression ] LOOP
statements
END LOOP [ label ];

Example:

DO $$
BEGIN
FOR a IN 1..5 LOOP
RAISE NOTICE 'a: %', a;
END LOOP;
END; $$