Parameters and return values

The CREATE FUNCTION and CREATE PROCEDURE statements specify any parameters and return values for a C UDR.

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. Table 1 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.

Tip: If the C implementation of your UDR requires an MI_FPARAM structure in its declaration, omit this structure from the parameter list of the CREATE FUNCTION or CREATE PROCEDURE statement. For more information about when a C UDR requires an MI_FPARAM structure, see The MI_FPARAM argument.

For more information about how to declare a C UDR, see Code a C UDR.