The rules and restrictions are:
- You can only include table functions in the FROM clause. See FROM Clause.
The system processes table functions like derived tables.
- The SELECT statement syntax supports the implicit specification of the number of return columns at runtime in its TABLE (function_name RETURNS table_name) clause.
- When you reference a table function that does not have a variable number of output columns, the system gets its explicit output column definitions from DBC.TVFields.
However, when you reference a table function that outputs a dynamic number of columns, you must specify either an output column list or the name of an existing table as part of the function invocation using the RETURNS table_name option in the FROM clause of the SELECT request.
| SELECT Specification | Columns Returned |
|---|---|
| Outcome column name list | Specified in the list as the function output columns. |
| Name of an existing table | From the definition for that table as the function output columns. |
In either case, the number of columns specified cannot exceed the defined maximum number of output columns specified by the VARYING COLUMNS maximum_output_columns specification of its CREATE FUNCTION (Table Form) definition. See CREATE FUNCTION and REPLACE FUNCTION (Table Form).
- To invoke a varying column table function, you can:
- Specify a list of return column name-data type pairs.
- Specify the return columns from an existing table.
For examples of both forms returning the same result, see Example: Dynamic Row Results Returned by Specifying Table Functions.
- You cannot specify LOB columns using the BLOB AS LOCATOR or CLOB AS LOCATOR forms.