PL/pgSQL control structures: if-else, case, loop
Introduction
In this section, we describe all the control structures statements, control structures are probably the most useful part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way.
Conditionals statements
IF and CASE are two conditionals statements and they are used under certain conditions. Here is the syntax of IF statements (three forms) :
IF ... THEN
Syntax:
IF ... THEN ... ELSE
Syntax:
IF ... THEN ... ELSIF ... THEN ... ELSE
Two forms of CASE syntax:
Syntax:
CASE ... WHEN ... THEN ... ELSE ... END CASE
Syntax:
CASE WHEN ... THEN ... ELSE ... END CASE
IF-THEN
Syntax:
IF boolean-expression THEN
statements
END IF;
IF-THEN statements are the simplest form of IF. The statements between THEN and END IF will be executed if the condition is true. Otherwise, they are skipped.
Examples:
Code:
CREATE OR REPLACE FUNCTION ifstat()
RETURNS text AS $$
DECLARE
ss integer;
BEGIN
ss:= 0;
IF ss = 0 THEN
RETURN 'You have declared zero';
END IF;
END;
Sample Output:
postgres=# select ifstat();
ifstat
------------------------
You have declared zero
(1 row)
IF-THEN-ELSE
Code:
CREATE OR REPLACE FUNCTION ifstat (date)
RETURNS text
AS
$$
BEGIN
IF EXTRACT(DAY FROM current_date) = 1
THEN
RETURN '1st day of the Month';
ELSE
RETURN 'Other day';
END IF;
END;
$$
LANGUAGE plpgsql;
To see the result from the function-
postgres=# SELECT _ifstat1(current_date); ifstat ----------- Other day (1 row)
IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition is not true. (Note this includes the case where the condition evaluates to NULL.)
Here is the sample table employee
empno | emp_first_name | emp_last_name | designame | dt_birth | mngr_no | dt_join | salary | commission | deduction | deptno -------+-----------------+-----------------+-----------------+------------+---------+------------+----------+------------+-----------+-------- 8328 | DAMAS | FORK | SALESMAN | 1983-10-12 | 8743 | 2006-06-14 | 7000.00 | 1500.00 | 600.00 | 25 8450 | HARRY | DIGGA | MANAGER | 1975-09-05 | 8640 | 1999-09-15 | 20000.00 | | 1200.00 | 15 8425 | JONE | KALE | CLERCK | 1983-08-06 | 8653 | 2004-02-03 | 11000.00 | | 700.00 | 45 8640 | ZOLE | NELSON | PRESIDENT | 1970-08-03 | | 1996-03-28 | 35000.00 | | 1200.00 | 15 8639 | JAMES | PETRO | SALESMAN | 1985-04-15 | 8653 | 2007-11-13 | 9000.00 | 1700.00 | 650.00 | 15 8744 | DAWIZ | DONALD | CLERCK | 1980-03-13 | 8743 | 2002-05-12 | 10000.00 | | 700.00 | 35 8752 | FARIN | JAYOS | MANAGER | 1972-10-12 | 8640 | 1997-07-14 | 22500.00 | | 900.00 | 25 8743 | SCOTT | NATIM | OFFICER | 1975-03-14 | 8861 | 2000-05-23 | 26000.00 | | 1400.00 | 15 8892 | WALLCOT | HILL | SALESMAN | 1982-11-15 | 8653 | 2001-12-16 | 8000.00 | 1200.00 | 650.00 | 25 8861 | TURPEO | DOGS | MANAGER | 1975-07-06 | 8640 | 1998-09-17 | 21000.00 | | 1000.00 | 25 8875 | CAMLI | PALIN | CLERCK | 1973-12-17 | 8653 | 1999-06-18 | 11000.00 | | 600.00 | 45 8869 | FASCO | STEAVE | ANALYTICS | 1981-02-18 | 8752 | 2002-12-24 | 18000.00 | | 800.00 | 15 8950 | CADRIM | KATEN | CLERCK | 1976-10-17 | 8653 | 2003-06-16 | 11500.00 | | 500.00 | 25 8653 | DANON | FORGE | OFFICER | 1971-07-02 | 8752 | 1994-10-17 | 25000.00 | | 1000.00 | 35 (14 rows)
Example:
Code:
CREATE OR REPLACE FUNCTION no_emp(integer,integer) RETURNS integer AS '
DECLARE
emp_id ALIAS FOR $1;
tot_dept ALIAS FOR $2;
tmp_id integer;
no_emp integer;
BEGIN
SELECT INTO tmp_id mngr_no FROM employee WHERE
empno = emp_id;
IF tmp_id IS NULL THEN
RETURN -1;
END IF;
SELECT INTO no_emp count(*) FROM employee WHERE deptno=tot_dept;
RETURN no_emp;
END;
' LANGUAGE 'plpgsql';
Sample Output:
postgres=# SELECT no_emp(8640,15) from employee;
no_emp
--------
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
(14 rows)
IF-THEN-ELSEIF :
Syntax:
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements ]
END IF;
IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition is not true. (Note this includes the case where the condition evaluates to NULL.)
Example:
Code:
CREATE FUNCTION ret_date (crdate date) RETURNS text AS $$
BEGIN
IF EXTRACT (MONTH FROM crdate)= 1
THEN RETURN 'January';
ELSIF EXTRACT (MONTH FROM crdate)= 2
THEN RETURN 'February';
ELSIF EXTRACT (MONTH FROM crdate)= 3
THEN RETURN 'March';
ELSIF EXTRACT (MONTH FROM crdate)= 4
THEN RETURN 'April';
ELSIF EXTRACT (MONTH FROM crdate)= 5
THEN RETURN 'May';
ELSIF EXTRACT (MONTH FROM crdate)= 6
THEN RETURN 'June';
ELSIF EXTRACT (MONTH FROM crdate)= 7
THEN RETURN 'July';
ELSIF EXTRACT (MONTH FROM crdate)= 8
THEN RETURN 'August';
ELSIF EXTRACT (MONTH FROM crdate)= 9
THEN RETURN 'September';
ELSIF EXTRACT (MONTH FROM crdate)= 10
THEN RETURN 'October';
ELSIF EXTRACT (MONTH FROM crdate)= 11
THEN RETURN 'November';
ELSIF EXTRACT (MONTH FROM crdate)= 12
THEN RETURN 'December';
END IF;
END;
$$
LANGUAGE plpgsql
Sample Output:
postgres=# SELECT ret_date(CURRENT_DATE);
ret_date
-----------
September
(1 row)
postgres=# select ret_date('2014-05-05');
ret_date
----------
May
(1 row)
Simple CASE
Syntax:
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The simple form of CASE provides conditional execution based on equality of operands. The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses. If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no match is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
Examples:
Code:
SELECT salary,
CASE WHEN department_id =90 THEN 'High Salary'
WHEN department_id =100 THEN '2nd grade salary'
ELSE 'Low Salary'
END
AS salary_status
FROM employees
LIMIT 15;
Sample Output:
salary | salary_status ----------+------------------ 24000.00 | High Salary 17000.00 | High Salary 17000.00 | High Salary 9000.00 | Low Salary 6000.00 | Low Salary 4800.00 | Low Salary 4800.00 | Low Salary 4200.00 | Low Salary 12000.00 | 2nd grade salary 9000.00 | 2nd grade salary 8200.00 | 2nd grade salary 7700.00 | 2nd grade salary 7800.00 | 2nd grade salary 6900.00 | 2nd grade salary 11000.00 | Low Salary (15 rows)
Searched CASE
Syntax:
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The searched form of CASE provides conditional execution based on the truth of Boolean expressions. Each WHEN clause's boolean-expression is evaluated in turn until one is found that yields true. Then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no true result is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
Examples:
Code:
CREATE OR REPLACE FUNCTION myfunc1 (x integer) RETURNS text AS $$
DECLARE
msg text;
BEGIN
CASE
WHEN x IN(2,4,6,8,10) THEN
msg := 'value even number';
WHEN x IN(3,5,7,9,11) THEN
msg := 'value is odd number';
END CASE;
RETURN msg;
END;
$$
LANGUAGE plpgsql
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunc1(5);
myfunc1
---------------------
value is odd number
(1 row)
Loops
Loops, may also call conditional statements, which can control the flow of functions. Loops use to repeat a number of different ways to achieve tasks, and through the use of repetition, you can greatly expand the functionality of a PL/pgSQL function. PL/pgSQL implements three iterative loops: the basic loop, quite advanced WHILE loop, and the FOR loop.
With the LOOP, EXIT, CONTINUE, WHILE, FOR, and FOREACH statements, you can arrange for your PL/pgSQL function to repeat a series of commands.
The basic loop
The keyword LOOP is used to start a basic, unconditional loop within a function. The basic job of an unconditional loop is to execute the statements within its body until it reaches to an EXIT statement. To reach to an EXIT statement, the EXIT keyword is required along with WHEN, and followed by and an expression which holds the condition to reach the EXIT from a loop.
Here is the syntax (without the ELSE keyword) for an unconditional loop:
Syntax:
<
LOOP
statement;
[...]
END LOOP;
An unconditional loop statement will do the repetition until it reaches an EXIT statement. When a loop is terminated with EXIT, you may optionally specify a label and optionally a condition on which the loop should exit from.
Here is an Syntax: of a defined loop with label :
[ <<label>> ]
LOOP
[ ... ]
END LOOP;
A label can help you to specify which loop to exit when you have more than loops nested each other and the condition decides when the loop should be terminated depending on its return of true or false.
Here is the syntax for an EXIT statement, within a LOOP:
Syntax:
[ <<label>> ]
LOOP
statement;
[...]
EXIT [ label ] [ WHEN condition ];
END LOOP;
Examples:
Code:
CREATE OR REPLACE FUNCTION myfunction(integer)
RETURNS integer AS '
DECLARE
nm ALIAS FOR $1;
cub integer;
BEGIN
cub := nm;
LOOP
cub := cub * cub * cub;
EXIT WHEN cub >= 10000;
END LOOP;
RETURN cub;
END;
' LANGUAGE 'plpgsql';
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunction(5);
myfunction
------------
1953125
(1 row)
The WHILE loop
The WHILE loop is used to do the job repeatedly within the block of statements until the condition mentioned becomes false. In this type of loop the condition mentioned will be executed first before the statement block is executed.
Here is t he syntax of the WHILE loop:
Syntax:
[ <<label>> ] WHILE condition LOOP statement; [...] END LOOP;
Examples:
Code:
CREATE OR REPLACE FUNCTION myfunction(integer)
RETURNS integer AS '
DECLARE
nm ALIAS FOR $1;
cub INTEGER;
BEGIN
cub:=nm;
WHILE cub <=10000 LOOP
cub := cub * cub * cub;
END LOOP;
RETURN cub;
END;
' LANGUAGE 'plpgsql';
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunction(5);
myfunction
------------
1953125
(1 row)
The FOR loop
Use the FOR loop to repeat a specific statement(s) within a block over a range specified terms.
In a PL/pgSQL FOR loop it is needed to initial an integer variable , to track the repetition of the loop, then the integer final value is given, and finally a statement block is provided within the loop.
Here is the syntax of the FOR loop:
Syntax:
[ <<label>> ]
FOR identifier IN [ REVERSE ] expression1 .. expression2 LOOP
statement;
[...]
END LOOP;
Parameters:
| Name | Description |
| identifier | The identifier is used to track the repetition of a job of the statement(s). It's value will be incremented by one if REVERSE is not specified otherwise it will be decremented. |
| expression1,expression2 | The initial and final value of identifier |
Example:
Code:
CREATE OR REPLACE FUNCTION myfunction(integer)
RETURNS integer AS '
DECLARE
nm ALIAS FOR $1;
cub INTEGER;
BEGIN
cub :=nm;
FOR i IN 0..10000 LOOP
cub := cub * cub * cub;
END LOOP;
RETURN cub;
END;
' LANGUAGE 'plpgsql';
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunction(5);
myfunction
------------
1953125
(1 row)
Here is another example:
Code:
CREATE OR REPLACE FUNCTION myfunction(dt DATE)
RETURNS INTEGER
AS
$$
DECLARE ddt DATE;
num INTEGER;
x INTEGER;
BEGIN
ddT := dt;
x := EXTRACT(MONTH FROM dt);
FOR i IN 1 .. 31
LOOP
num := i;
EXIT WHEN EXTRACT(MONTH FROM ddt + i * INTERVAL '1 DAY') <> x;
END LOOP;
RETURN num-1;
END;
$$
LANGUAGE PLPGSQL
Here is the execution of the function
Sample Output:
postgres=# SELECT myfunction(current_date);
myfunction
------------
17
(1 row)
The FOR loop can also be used to circulate through the results of a query. The FOR loop also works with RECORD and %ROWTYPE variables.
Here is the syntax of a FOR loop that repeats through RECORD and %ROWTYPE variables.
Syntax:
[ <<label>> ]
FOR { record_variable | %rowtype_variable } IN select_statement LOOP
statement;
[...]
END LOOP;
Examples:
Code:
CREATE OR REPLACE FUNCTION myfunction(integer) RETURNS text AS '
DECLARE
depid ALIAS FOR $1;
output_txt TEXT :=''\n'';
row_data employees%ROWTYPE;
BEGIN
FOR row_data IN SELECT * FROM employees
WHERE department_id = depid ORDER BY first_name LOOP
output_txt := output_txt || row_data.first_name || row_data.last_name || ''\n'';
END LOOP;
RETURN output_txt;
END;
' LANGUAGE 'plpgsql';
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunction(90);
myfunction
------------------------------------------
\nLexDe Haan\nNeenaKochhar\nStevenKing\n
(1 row)
Previous: Basic Statements
Next: PostgreSQL Sequence
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/PostgreSQL/pl-pgsql-control-structures.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics