INFORMIX
Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 3: Designing a User-Defined Routine
Home Contents Index Master Index New Book

Returning Multiple Values from External Functions

The database server provides two methods for external functions to return multiple values:

OUT Parameters and Statement Local Variables

 The database server supports external functions that return more than one value. Use the OUT parameter in the CREATE FUNCTION statement to pass a pointer to the address to which the function can write extra values.

To return extra values from an external function with the OUT parameter

    1. Write an external function that returns more than one value to the caller.

    The function must accept an extra parameter that is a pointer to the address to which the function can write. This pointer must be the last parameter.

    2. Register the function with the OUT keyword to indicate that the function returns extra values.

    The OUT keyword indicates that the last parameter passes a pointer.

CREATE FUNCTION my_func(x INT, OUT y INT)

RETURNING INT

EXTERNAL NAME "/usr/lib/local_site.so"

LANGUAGE C

END FUNCTION;

    3. Create a Statement Local Variable (SLV) to use the OUT parameter when you invoke the external function in an SQL expression.

    Each SLV gives a variable name to one of the values dereferenced from the OUT pointer.

Important: An SLV is valid only for the life of a single statement.

Referencing OUT Parameters in User-Defined Routines

 Each SLV argument in an SQL statement has a unique name. You can use the SLV name to dereference a return value multiple times within that statement. While each function can pass only one OUT, a single SQL statement in the calling routine can invoke multiple functions with OUT parameters. For example, the following partial statement receives pointers from two functions with OUT parameters, which are referenced with the SLV names out1 and out2:

The calling function does not pass any data values to the OUT parameter. At the time the function is called, the SLV does not reference any actual values. The calling routine passes only a pointer to some space where the function can store values. An external function cannot retrieve values from the reserved space. By contrast, a pointer passed by a pure C-language program can point to a valid value, and the function can examine the current value before it modifies and returns the pointer.

Important: The function that receives the pointer should not try to read from this address because the value is meaningless.
If the function that sources the SLV is not executed in an iteration of the statement, the SLV has a value of NULL. SLV values do not persist across iterations of the statement. At the start of each iteration, the SLV value is set to NULL.

Because an SLV shares the name space with procedure variables and columns names of the table involved in the statement, a priority has been established to determine which one takes precedence in ambiguous situations. Procedure variables have the highest precedence, and SLVs have the lowest precedence.

Iterator Function

An iterator function returns more than one row of values. The database server automatically invokes the function repeatedly, one for each row of return values. An iterator function is similar to an SPL function that contains the RETURN WITH RESUME statement.

Writing an Iterator Function

When you write an iterator function, you use DataBlade API functions (such as mi_fp_setisdone() and mi_fp_request()) to handle each return row. For more information on writing an iterator function, refer to the DataBlade API Programmer's Manual.

Registering an Iterator Function

By default, an external function is not an iterator. To define an iterator function, you must register the function with the ITERATOR modifier.

The following sample CREATE FUNCTION statement shows how to register the function TopK as an iterator function:

Invoking an Iterator Function

 You can invoke an iterator function with one of the following methods:



Extending INFORMIX-Universal Server: User-Defined Routines, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.