The control structure of PL/pgSQL is the most important and useful part. In the actual working scenario, it is inseparable from the business processing logic. When writing PL/pgSQL, the control structure is used to operate data. The control structure supported by PL/pgSQL is almost the same as that of other languages, such as condition, loop, exception, etc. the following describes the use of control structure respectively.

1. Conditions

For conditional control structures, IF and CASE are supported. IF statements can be written in the following three ways:

if ... then ... end if
if ... then ... else ... end if 
if ... then ... elsif ... then ... else ... end if

And CASE can be written in two ways:

case ... when ... then ... else ... end case
case when ... then ... else ... end case

1.1,IF-THEN

IF Conditional expression THEN
	Main part
END IF

IF-THEN is the simplest form of IF structure. IF the condition is true, the script of the main part will be executed, otherwise it will not be executed. For example:

IF id = 1 THEN
	update t_order set order_code = '123' where id = 1;
END IF;

1.2,IF-THEN-ELSE

IF Conditional expression THEN
	Main part
ELSE
	Main part
END IF;

IF-THEN-ELSE statementwhen the conditional expression is false, the script of ELSE part is executed. For example:

IF i_name is not null THEN
	select account into r_account from t_user t where t.name = i_name ;
ELSE
	raise notice 'User name is empty';
END IF;

1.3,IF-THEN-ELSIF

IF Conditional expression THEN
	Main part
ELSIF Conditional expression THEN
	Main part
ELSE 
	Main part
END IF;

IF-THEN-ELSIF supports multiple conditions. For example, if the first condition is not satisfied, enter the second condition. Otherwise, execute the ELSE part. Of course, the ELSE part can not be written.

IF i_n < 13 THEN
	raise notice 'primary school';
ELSIF i_n >= 13 and i_n <16 THEN
	raise notice 'junior high school';
ELSE 
	raise notice 'finished';
END IF;

1.4,CASE

The CASE of the control structure is written in the same way as the CASE in the SQL statement, for example:

CASE Search parameters
	WHEN value THEN Main part
	ELSE Main part
END CASE;
perhaps
CASE
	WHEN Conditional expression THEN Main part
	ELSE Main part
END CASE;

In the first way, if the search parameter is in the given value, execute the statement after THEN. Otherwise, execute the ELSE part, and the ELSE part can be omitted.
The second chapter is the writing method. The qualified expressions execute the statements after THEN, otherwise execute the ELSE part. For example:

CASE account
	WHEN 'zhangsan','lisi' THEN
		msg := 'Account number is zhangsan or lisi'
	ELSE
		msg := 'Account not found'
END CASE;
or
CASE 
	WHEN account in ('zhangsan','lisi') THEN
		msg := 'Account number is zhangsan or lisi'
	ELSE
		msg := 'Account not found'
END CASE;

The function of CASE statement is completely consistent with that of IF-THEN-ELSIF.

2. Circulation

In PL/pgSQL, LOOP, FOR and WHILE can be used to realize the LOOP, and EXIT and CONTINUE control the LOOP.

2.1 simple cycle

[<<label>>]
LOOP
	Main part
END LOOP [label];

Using LOOP directly to define a LOOP is an unconditional LOOP. If so defined, executing this statement will enter an infinite LOOP. The LOOP can be terminated only if EXIT and RETURN are written in the body. In actual scenarios, this unconditional LOOP is rarely used. The label here is optional. If a label is added, it can be used by EXIT and CONTINUE statements, such as nested loops. If the innermost LOOP wants to jump directly to the label defined on the outermost layer, it can use EXIT label to jump to the specified label position.

2.1.1 ,EXIT

EXIT [label] [WHEN Conditional expression]

There are two optional parts after EXIT. If the label part is given, EXIT to the specified label. If it is not specified, EXIT the current cycle. If the WHEN part is qualified, EXIT.

2.1.2,CONTINUE

CONTINUE [label] [WHEN Conditional expression]

The syntax of CONTINUE is similar to EXIT, and the functions of the following two parts are the same. The only difference is that EXIT exits the cycle, and CONTINUE exits this cycle and enters the next cycle.

2.2 FOR cycle

2.2.1 simple cycle

[<<label>>]
FOR name IN [REVERSE] expression..expression [BY expression] LOOP
	Main part
END LOOP [label];

This form of FOR iterates over a range of integers. The variable name is automatically defined as BY type and only exists in the loop (any existing definition of the variable name will be ignored in this loop). The two expressions that give the upper and lower bounds of the range are calculated once when entering the loop. The BY clause specifies the iteration step size (the default is 1), but if REVERSE is declared, the step size will become the corresponding negative value.

--Cycle output 1..10
for i in 1..10 loop
	raise notice '%',i;
end loop;
--From 10..1 Output, you need to add reverse
for i in reverse 10..1 loop
	raise notice '%',i;
end loop;
--From 1..10，Every two outputs
for i in 1..10 by 2 loop
	raise notice '%',i;
end loop;

2.2.2. Circular query results

Circular query results are used more in actual scenarios. The syntax is as follows:

[<<label>>]
for target in Query statement loop
	Main part
end loop [label];

target is a record variable, which must be declared in the declare part first, such as user information: us_info record, the complete case is as follows:

do $$
declare
	t_o record;
begin
	for t_o in (select * from t_order) loop 
		raise notice 'ID:%,customer:%',t_o.id,t_o.cus_name;
	end loop;
end $$;

2.3 WHILE cycle

[<<label>>]
while Conditional expression loop
	Main part
end loop [label]

while loop: as long as the conditional expression is met, the loop will continue. For example:

do $$
declare
	i int := 10;
begin
	while i != 0 loop
		raise notice '%',i;
		i := i-1;
	end loop;
end $$;

3. Abnormal

No program can fail. However, sometimes we need to catch exceptions to continue processing or better prompt the user with exception information. The PL/pgSQL exception structure is as follows:

[<<label>>]
[declare
 Declaration part]
begin
	Main part
exception
	when Exception name then
		exception handling
end [label];

As long as there is an exception, it will enter the exception section and be recognized in the when for, such as:

do $$
declare
	x int:=6;
begin
	x := x/0;
exception 
	when division_by_zero  then
		raise notice 'Divisor cannot be 0';
end $$;