The Builders: A Postgres Podcast
Discussions about Postgres, Data and AI featuring EDB Technical Staff.
Futures in Focus (CMO Michael Gale)
Spotlights on how visionaries and leaders are thinking about tomorrow's world.
SUMMARY: This article reviews control structures that can be used in PostgreSQL stored procedures, with syntax and examples provided for each.
1. IF statements
a. Simple IF statements
b. IF-THEN-ELSE statements
2. CASE statements
3. LOOP statements
Stored procedures in PostgreSQL are ones that define a function for creating triggers or custom functions. There are three main types of control structures available with PostgreSQL to use with stored procedures: IF, CASE, and LOOP.
IF statements
1. Simple IF statements
Syntax
IF condition THEN
statement;
END IF;
The IF condition runs when a condition is evaluated as true. If the condition is false then it goes to the next statement after END IF.
Example
DO $$
DECLARE
x integer := 10;
y integer := 20;
BEGIN
IF x > y THEN
RAISE NOTICE 'x is greater than y';
END IF;
IF x < y THEN
RAISE NOTICE 'x is less than y';
END IF;
IF x = y THEN
RAISE NOTICE 'x is equal to y';
END IF;
END $$;
The above example declares 2 variables at the start. The x and y have been assigned a value. That is, x=10 and y=20.
Next, there are three conditions: when x is greater than y, x is less than y, and x is equal to y. Depending upon the condition satisfied it will raise a notice—in this case, “x is less than y.”
2. IF-THEN-ELSE statements
Syntax
IF condition THEN
statements;
additional statements;
END IF;
Example
DO $$
DECLARE
x integer := 10;
y integer := 20;
BEGIN
IF x > y THEN
RAISE NOTICE 'x is greater than y';
RAISE NOTICE 'x is not greater than y';
END IF;
END $$;
As in the first example, here 2 variables are declared at the start: x=10 and y=20.
Next, there is one condition: when x is greater than y it raises notice that “x is greater than y.” When that condition is not met, it raises the notice “x is not greater than y.” In this case, the condition is not met, so the ELSE clause is executed and the output for the ELSE part is printed.
CASE statements
The CASE statement uses IF-THEN-ELSE logic within a single statement. It facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement and applying it to many possible conditions.
Syntax
CASE condition
WHEN condition value THEN statement
ELSE additional statement;
Example
We can use CASE to evaluate multiple conditions for a single variable, “job_id.” If “job_id” is “ACCOUNT,” the salary increase is 10%; if “job_id” is “IT_PROG,” the salary increase is 15%; if “job_id” is “SALES,” the salary increase is 20%. For all other job roles, there is no increase in salary.
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'ACCOUNT' THEN 1.10*salary
WHEN 'IT_PROG' THEN 1.15*salary
WHEN 'SALES' THEN 1.20*salary
ELSE salary END "REVISED_SALARY" FROM employees;
LOOP statements
Syntax
<execution block starts>
<EXIT condition based on requirement>
<execution_block_ends>
END LOOP;
The LOOP keyword declares the beginning of the loop, and END LOOP declares the end of the loop. The execution block contains all the code that needs to be executed, including the EXIT condition.
Example
