The return statement terminates the execution of the current function and returns to the routine that called the function. You are allowed to use the return keyword elsewhere in the code besides at the end of the function body. For instance, the return keyword can be used at the closing of a function body to display a data value.

Use this syntax to RETURN from a function:

   RETURN 

expression

;
   

For example, this function called avg3() returns the result of an average operation on three numbers:

Example 9-13. Returning from a PL/pgSQL Function

   CREATE FUNCTION avg3(integer, integer, integer) RETURNS integer AS'
    DECLARE
       -- defines an alias name 
       -- for the three inputs to be calculated
       num1 ALIAS FOR $1;
       num2 ALIAS FOR $2;
       num3 ALIAS FOR $3;

       -- defines an average variable to hold the answer
       avg integer;

    BEGIN
       -- adds the 3 numbers together and divides by 3
       -- assigns the result to the avg variable
       avg := ((num1 + num2 + num3)/3);

       -- displays the average result 
       return avg;

    END;
  ' LANGUAGE 'plpgsql'; 
   

The avg3 () function takes in three numbers and then adds them together and divides by 3 to get the average. It then puts that result into the avg variable.

When this function is invoked, and supplied with the numbers: 21 , 29, and 10, it returns:

 
        avg3
       ------
         20
       (1 row)
       

This is the correct answer, but when supplied with the numbers: 21, 30, and 10, it displays:

        avg3
       ------
         20
       (1 row)
       

How can this be? It is because throughout the entire function, the avg variable and return type is declared as an integer. Therefore, the result of the average operation will always return a whole number, but not the numbers after the decimal point.

To fix this, there are several ways you can approach the problem:

One way is to do an explicit type cast.

You can cast the integer values into a numeric type and make it decimal. This casts the average math operation into a decimal number:

       avg := ((num1 + num2 + num3)/3::numeric); 
      

The second way is to change the arguments of the function into numeric.

This changes all three input arguments into numeric:

       CREATE FUNCTION avg3(numeric, numeric, numeric) .....
      

With either method, you will need to change the avg variable and define the function to return a numeric data type. If the second method is used, the function should look like this:

Example 9-14. PL/pgSQL avg3() Function

   CREATE FUNCTION avg3(numeric, numeric, numeric) RETURNS numeric AS'
    DECLARE
       -- defines an alias name 
       -- for the three inputs to be calculated
       num1 ALIAS FOR $1;
       num2 ALIAS FOR $2;
       num3 ALIAS FOR $3;

       -- defines an average variable to hold the answer
       avg numeric;

    BEGIN
       -- adds the 3 numbers together and divides by 3
       -- assigns the result to the avg variable
       avg := ((num1 + num2 + num3)/3);

       -- displays the average result 
       return avg;

    END;
  ' LANGUAGE 'plpgsql'; 
   

Once again, try supplying the numbers: 21, 30, and 10. It outputs the correct answer:

        avg3
     ---------------
      20.3333333333
     (1 row)
      

Once this function terminates, the value of the expression is returned to the parser. Therefore, a return value must be defined. If not, a runtime error will occur.