PL pgSQL basic statements if else case loop

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE

Two forms of CASE syntax :

CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE

IF-THEN

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 :

  1. CREATE OR REPLACE FUNCTION ifstat()  
  2. RETURNS text AS $$  
  3. DECLARE  
  4. ss integer;  
  5. BEGIN  
  6. ss:= 0;  
  7. IF ss = 0 THEN   
  8. RETURN 'You have declared zero';  
  9. END IF;  
  10. END;  

To see the result from the function-

postgres=# select ifstat();
         ifstat
------------------------
 You have declared zero
(1 row)

IF-THEN-ELSE

  1. CREATE OR REPLACE FUNCTION ifstat (date)   
  2. RETURNS text   
  3. AS   
  4. $$   
  5. BEGIN   
  6.    IF EXTRACT(DAY FROM current_date) = 1   
  7.    THEN     
  8.       RETURN '1st day of the Month';   
  9.    ELSE   
  10.       RETURN 'Other day';   
  11.    END IF;   
  12. END;   
  13. $$   
  14. 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 :

  1. CREATE OR REPLACE FUNCTION no_emp(integer,integerRETURNS integer AS '  
  2.   DECLARE  
  3.     emp_id ALIAS FOR $1;  
  4.     tot_dept ALIAS FOR $2;  
  5.     tmp_id integer;  
  6.     no_emp integer;  
  7.   BEGIN  
  8.     SELECT INTO tmp_id mngr_no FROM employee WHERE  
  9.       empno = emp_id;  
  10.     IF tmp_id IS NULL THEN  
  11.       RETURN -1;  
  12.     END IF;  
  13.     SELECT INTO no_emp count(*) FROM employee WHERE deptno=tot_dept;  
  14.     RETURN no_emp;  
  15.     
  16.   END;  
  17. ' LANGUAGE 'plpgsql';  

Here is the 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 :

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 :

  1. CREATE FUNCTION ret_date (crdate dateRETURNS text AS $$   
  2. BEGIN   
  3.    IF EXTRACT (MONTH FROM crdate)= 1   
  4.    THEN RETURN 'January';   
  5.    ELSIF EXTRACT (MONTH FROM crdate)= 2   
  6.    THEN RETURN 'February';   
  7.    ELSIF EXTRACT (MONTH FROM crdate)= 3   
  8.    THEN RETURN 'March';   
  9.    ELSIF EXTRACT (MONTH FROM crdate)= 4   
  10.    THEN RETURN 'April';   
  11.    ELSIF EXTRACT (MONTH FROM crdate)= 5   
  12.    THEN RETURN 'May';   
  13.    ELSIF EXTRACT (MONTH FROM crdate)= 6   
  14.    THEN RETURN 'June';   
  15.    ELSIF EXTRACT (MONTH FROM crdate)= 7   
  16.    THEN RETURN 'July';   
  17.    ELSIF EXTRACT (MONTH FROM crdate)= 8  
  18.    THEN RETURN 'August';   
  19.    ELSIF EXTRACT (MONTH FROM crdate)= 9   
  20.    THEN RETURN 'September';   
  21.    ELSIF EXTRACT (MONTH FROM crdate)= 10   
  22.    THEN RETURN 'October';   
  23.    ELSIF EXTRACT (MONTH FROM crdate)= 11  
  24.    THEN RETURN 'November';   
  25.    ELSIF EXTRACT (MONTH FROM crdate)= 12  
  26.    THEN RETURN 'December';   
  27.    END IF;   
  28. END;   
  29. $$   
  30. LANGUAGE plpgsql  

Here is the 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

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 :

  1. SELECT salary,  
  2.      CASE WHEN department_id =90 THEN 'High Salary'               
  3.      WHEN department_id =100 THEN '2nd grade salary'              
  4.      ELSE 'Low Salary'          
  5.      END   
  6.      AS salary_status         
  7.      FROM employees         
  8.      LIMIT 15;  

Here is the 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

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 :

  1. CREATE OR REPLACE FUNCTION myfunc1 (x integerRETURNS text AS $$   
  2. DECLARE  
  3. msg text;  
  4. BEGIN   
  5. CASE  
  6.     WHEN x IN(2,4,6,8,10) THEN  
  7.         msg := 'value even number';  
  8.     WHEN x IN(3,5,7,9,11) THEN  
  9.         msg := 'value is odd number';  
  10. END CASE;  
  11. RETURN msg;  
  12. END;   
  13. $$   
  14. LANGUAGE plpgsql  

Here is the execution of the above function.

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:

  [ <<label>> ]
  LOOP
    statement;
    [...]
    EXIT [ label ] [ WHEN condition ];
  END LOOP;

Examples :

  1. CREATE OR REPLACE FUNCTION myfunction(integer)   
  2. RETURNS integer AS '  
  3.   DECLARE  
  4.     nm ALIAS FOR $1;  
  5.     cub integer;  
  6.   BEGIN  
  7.     cub := nm;  
  8.     LOOP  
  9.       cub := cub * cub * cub;  
  10.       EXIT WHEN cub >= 10000;  
  11.     END LOOP;  
  12.     RETURN cub;  
  13.   END;  
  14. ' LANGUAGE 'plpgsql';  

Here is the execution of the above function.

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:

[ <<label>> ]    
WHILE condition LOOP      
statement;      
[...]    
END LOOP;

Examples :

  1. CREATE OR REPLACE FUNCTION myfunction(integer)   
  2. RETURNS integer AS '  
  3.   DECLARE  
  4.    nm ALIAS FOR $1;  
  5.     cub INTEGER;  
  6.   BEGIN  
  7.    cub:=nm;  
  8.     WHILE cub <=10000 LOOP  
  9.       cub := cub * cub * cub;  
  10.     END LOOP;  
  11.     RETURN cub;  
  12.   END;  
  13. ' LANGUAGE 'plpgsql';  

Here is the execution of the above function.

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:

  [ <<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:

  1. CREATE OR REPLACE FUNCTION myfunction(integer)   
  2. RETURNS integer AS '  
  3.   DECLARE  
  4.    nm ALIAS FOR $1;  
  5.     cub INTEGER;  
  6.   BEGIN  
  7.    cub :=nm;  
  8.    FOR i IN 0..10000 LOOP  
  9.       cub := cub * cub * cub;  
  10.     END LOOP;  
  11.     RETURN cub;  
  12.   END;  
  13. ' LANGUAGE 'plpgsql';  

Here is the execution of the above function.

postgres=# SELECT myfunction(5);
 myfunction
------------
    1953125
(1 row)

Here is another example :

  1. CREATE OR REPLACE FUNCTION myfunction(dt DATE)   
  2. RETURNS INTEGER   
  3. AS   
  4. $$   
  5. DECLARE ddt DATE;   
  6.         num  INTEGER;   
  7.         x  INTEGER;   
  8. BEGIN   
  9.    ddT := dt;   
  10.    x := EXTRACT(MONTH FROM dt);   
  11.    FOR i IN 1 .. 31   
  12.    LOOP    
  13.       num := i;            
  14.       EXIT WHEN EXTRACT(MONTH FROM ddt + i * INTERVAL '1 DAY') <> x;   
  15.    END LOOP;   
  16.    RETURN num-1;      
  17. END;   
  18. $$   
  19. LANGUAGE PLPGSQL  

Here is the execution of the function

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.

  [ <<label>> ]
  FOR { record_variable | %rowtype_variable } IN select_statement LOOP
    statement;
    [...]
  END LOOP;

Examples :

  1. CREATE OR REPLACE FUNCTION myfunction(integerRETURNS text AS '  
  2.   DECLARE  
  3.       depid ALIAS FOR $1;  
  4.     output_txt TEXT :='' '';  
  5.     row_data employees%ROWTYPE;  
  6.   BEGIN  
  7.     FOR row_data IN SELECT * FROM employees  
  8.     WHERE department_id = depid  ORDER BY first_name LOOP  
  9.       output_txt := output_txt || row_data.first_name || row_data.last_name || '' '';  
  10.     END LOOP;  
  11.     RETURN output_txt;  
  12.   END;  
  13. ' LANGUAGE 'plpgsql';  

Here is the execution of the above function.

postgres=# SELECT myfunction(90);
                myfunction
------------------------------------------
 
LexDe Haan
NeenaKochhar
StevenKing

(1 row)