Parameters and return values

For user-defined functions, the RETURN clause of the CREATE FUNCTION statement specifies the return value. Use SQL data types for parameters and the return value. These SQL data types must be compatible with the DataBlade® API data types in the routine declaration. DataBlade API, C, and SQL data types lists the SQL data types that correspond to the different DataBlade API data types.

For example, suppose you have a C UDR with the following C declaration:

mi_double_precision *func1(parm1, parm2)
   mi_integer parm1;
   mi_double_precision *parm2;

The following CREATE FUNCTION statement registers the func1() user-defined function:

CREATE FUNCTION func1(INTEGER, FLOAT)
RETURNS FLOAT;

Use the opaque SQL data type, POINTER, to specify a data type for a C UDR whose parameter or return type has no SQL data type equivalent. For example, suppose you have a C UDR that has the following C declaration:

my_private_struc *func2(parm1, parm2)
   mi_integer parm1, parm2;

The following CREATE FUNCTION statement registers the func2() user-defined function:

CREATE FUNCTION func2(INTEGER, INTEGER)
RETURNS POINTER;

This CREATE FUNCTION statement uses the POINTER data type because the data structure to which func2() returns a pointer is a private data type, not one that is surfaced to users by registering it in the database.