PL/pgSQL control structures: if-else, case, loop

Last update on May 02 2022 11:18:40 (UTC/GMT +8 hours)

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;

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