Add a return clause

If you use CREATE FUNCTION to create an SPL routine, you must specify a return clause that returns one or more values.

Tip: If you use the CREATE PROCEDURE statement to create an SPL routine, you have the option of specifying a return clause. Your code will be easier to read and to maintain, however, it you instead use the CREATE FUNCTION statement to create a routine that returns values.

To specify a return clause, use the RETURNING or RETURNS keyword with a list of data types the routine will return. The data types can be any SQL data types except SERIAL, SERIAL8, TEXT, or BYTE.

The return clause in the following figure specifies that the SPL routine will return an INT value and a REAL value.

Figure 1: Specify the return clause.

FUNCTION find_group(id INT)
   RETURNING INT, REAL;
. . .
END FUNCTION;

After you specify a return clause, you must also specify a RETURN statement in the body of the routine that explicitly returns the values to the calling routine. For more information on writing the RETURN statement, see Return values from an SPL function.

To specify that the function should return a simple large object (a TEXT or BYTE value), you must use the REFERENCES clause, as in the following figure, because an SPL routine returns only a pointer to the object, not the object itself.

Figure 2: Use of the REFERENCES clause.

CREATE FUNCTION find_obj(id INT)
   RETURNING REFERENCES BYTE;