Returning Values

A common use of a UDR is to return values to the calling SQL statement. A UDR that returns a value is called a user-defined function.

For information on how to specify the data type of the return value of a user-defined function, see Registering a User-Defined Routine.

Returning a Variant or Nonvariant Value

By default, a user-defined function is a variant function. A variant function has any of the following characteristics:

• It returns different results when it is invoked with the same arguments.

  For example, a function whose return value is computed based on the current date or time is a variant function.

• It has variant side effects, such as:
  • Modifying some database table, variable state, or external file
  • Failing to locate an external file, or a table or row in a database, and returning an error

You can explicitly specify a variant function with the VARIANT keyword. However, because a function is variant by default, this keyword is not required.

A nonvariant function always returns the same value when it receives the same argument, and it has none of the preceding variant side effects. Therefore, nonvariant functions cannot access external files or contain SQL statements, even if the SQL statements only SELECT static data and always return the same results. You specify a nonvariant function with the NOT VARIANT keywords.

You can create a functional index only on a nonvariant function. The return result for a functional index cannot contain a smart large object. Functional indexes are indexed on the value returned by the specified function rather than on the value of a column. The value returned by a functional index cannot contain a smart large object.

The database server can execute a nonvariant function during query compile time if all the arguments passed to it are constants. In that case, the result replaces the UDR expression in the query tree. This action by the database server is constant elimination. The database server cannot execute an SQL statement during constant elimination, thus a nonvariant function cannot execute even nonvariant SQL.

For information about creating a functional index, refer to the CREATE INDEX statement in the IBM Informix: Guide to SQL Syntax.

Using OUT Parameters and Statement-Local Variables (SLVs)

You use OUT parameters to pass values from the called function to the caller. The SPL, C, or Java called function sets the value of this parameter and returns a new value through the parameter. Any or all arguments of a UDR can be an OUT parameter. You cannot use OUT parameters to pass values to the called function; OUT parameters are passed as NULL to the UDR.

The syntax for creating a UDR with OUT parameters is:

CREATE FUNCTION udr ([IN/OUT] arg0 datatype0, ...,
                     [IN/OUT] argN datatypeN)
                     RETURNING returntype;
...
END FUNCTION;

By default, a parameter is considered an IN parameter unless you define it as an OUT parameter by specifying the OUT keyword.

For example, the following CREATE FUNCTION statement specifies one IN parameter, x, and two OUT parameters, y and z.

CREATE FUNCTION my_func(x INT, OUT y INT, OUT z INT) 
RETURNING INT
EXTERNAL NAME '/usr/lib/local_site.so'
LANGUAGE C

A statement-local variable (SLV) is an OUT parameter used in the WHERE clause of a SELECT statement. See Using SLVs for more information.

Using SLVs

An SLV transmits OUT parameters from a user-defined function to other parts of an SQL statement. An SLV is local to the SQL statement; that is, it is valid only for the life of the SQL statement. It provides a temporary name by which to access an OUT parameter value. Any or all user-defined function arguments can be an SLV.

In the SQL statement that calls the user-defined function, you declare the SLV with the syntax: SLV_name # SLV_type, where SLV_name is the name of the SLV variable and SLV_type is its data type, as in:

SELECT SLV_name1, SLV_nameN FROM table WHERE 
   udr (param1, SLV_name1 # SLV_type1, ...
        SLV_nameN # SLV_typeN, paramN);

For example, the following SELECT statement declares SLVs x and z that are typed as INTEGER in its WHERE clause and then accesses both SLVs in the projection list:

SELECT x, z WHERE my_func(x # INT, y, z # INT) < 100 
    AND (x = 3) AND (z = 5)

For more information on the syntax and use of an SLV, see the description of function expressions within the Expression section in the IBM Informix: Guide to SQL Syntax.

SPL Procedures With No Return Values

SPL procedures with no return values are only accessible through the JDBC CallableStatement interface. SPL procedures with no return values can use OUT parameters. The syntax for creating such a procedure is:

CREATE PROCEDURE spl_udr ([IN/OUT] arg0 datatype0, ...,
                         [IN/OUT] argN datatypeN);
...

END PROCEDURE;

For example, the following SQL statement creates an SPL procedure with two OUT parameters and one IN parameter:

CREATE PROCEDURE myspl (OUT arg1 int, arg2 int, OUT arg3 int);
LET arg1 = arg2;
LET arg3 = arg2 * 2;
END PROCEDURE;

SPL procedures that do not return values cannot be used in the WHERE clause of a SELECT statement and therefore cannot generate SLVs.

Using INOUT Parameters

Dynamic Server supports UDRs written in Java or C that have multiple INOUT parameters. When an INOUT parameter is used, the bind value passed by the client for the INOUT parameter is passed to the UDR and the modified value is retrieved and returned to the client. The parameter can be of any type that Dynamic Server supports, including user-defined types and complex types.

An example of a UDR with an INOUT parameter is:

CREATE PROCEDURE CALC ( INOUT Param1 float )
EXTERNAL NAME "$INFORMIXDIR/etc/myudr.so(calc)"
LANGUAGE C;
/* C code for the routine */
void calc ( mi_double_precision *Param1)
{
    #define PI 3.1415;
    Param1 *= PI;
    return;
}

You can use INOUT parameters in the CREATE FUNCTION statement, as shown in the following syntax:

    CREATE FUNCTION func ([IN|OUT|INOUT] arg0 DataType, ..., 
[IN|OUT|INOUT] argN  DataType) RETURNING ReturnType;
    .........
    END FUNCTION;