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:
Sample Output:
postgres=# select ifstat(); ifstat ------------------------ You have declared zero (1 row)
IF-THEN-ELSE
Code:
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:
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:
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:
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:
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:
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:
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:
Here is the execution of the above function.
Sample Output:
postgres=# SELECT myfunction(5); myfunction ------------ 1953125 (1 row)
Here is another example:
Code:
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:
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