This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.

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:

RETURNexpression;

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.

Continue to: