Linux x86-64 (RHEL 8)
Arun Gavhane Senior Systems Engineer Feb 12, 2020
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.
1. Simple IF statements
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.
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
IF condition THEN statements; ELSE additional statements; END IF;
DO $$ DECLARE x integer := 10; y integer := 20; BEGIN IF x > y THEN RAISE NOTICE 'x is greater than y'; ELSE 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.
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.
CASE condition WHEN condition value THEN statement ELSE additional statement;
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 <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.
DO $$ DECLARE a NUMBER:=1; BEGIN RAISE NOTICE 'Loop started.'; LOOP RAISE NOTICE 'a'; a:=a+1; EXIT WHEN a>5; END LOOP; RAISE NOTICE 'Loop completed'; END; END $$;