Syntax and usage
The syntax for using an iterator function in the FROM
clause is:
FROM TABLE (FUNCTION iterator_func_name ([argument_list]))
[[AS] virtual_table_name] [(virtual_column_list)]The virtual_table_name parameter
is unqualified (do not include the owner or database name) and specifies
the name of the virtual table that holds the result set from the iterator
function.
Important: The virtual table can only be referenced
within the context of this SELECT query. After the SELECT statement
completes, the virtual table no longer exists.
The virtual_column_list parameter is a comma-separated list of unqualified column names for the virtual table. The number of columns must match the number of values returned by the iterator (SPL functions can return more than one value).
If
you want to reference virtual table columns in other parts of the
SELECT statement, for example, in the projection list, WHERE clause,
or HAVING clause, you must specify the virtual table name and virtual
column names in the FROM clause. You do not have to specify the virtual
table name or column names in the FROM clause if you use wildcard
characters in the projection list of the SELECT clause:
SELECT * FROM ...As
an example, the following statement retrieves the result set from
the function called fibseries(). This result set
is held in the virtual table called vtab.
SELECT col FROM TABLE (FUNCTION fibseries(10)) vtab(col);If
a SELECT statement specifying an iterator in the FROM clause returns
unexpected results, execute the iterator function separately to verify
the function is behaving correctly. For example, run your function
in DB-Access with a command like this:
execute function iterator_udr(args) The SQL Explain output section for a virtual table derived from an iterator UDR is marked ITERATOR UDR SCAN.
Ensure that you call mi_fp_setisdone() in a C UDR or UDREnv.setSetIterationIsDone(true) in a JAVA UDR when the iterator UDR is finished. The server checks this flag internally to determine when to stop calling the iterator UDR.