Routines: Table functions

Table functions are functions that for a group of sets of one or more parameters, returns a table to the SQL statement that references it.

Table functions can only be referenced in the FROM clause of a SELECT statement. The table that is returned by a table function can participate in joins, grouping operations, set operation such as UNION, and any operation that could be applied to a read-only view.

Features

• Returns a set of data values for processing.
• Can be referenced as part of a SQL query.
• Can make operating system calls, read data from files or even access data across a network in a single partitioned database.
• Results of table function invocations can be directly accessed by the SQL statement that references the table function.
• SQL table functions can encapsulate SQL statements that modify SQL table data. External table functions cannot encapsulate SQL statements.
• For a single table function reference, a table function can be iteratively invoked multiple times and maintain state between these invocations by using a scratchpad.

Limitations

• Transaction management is not supported within user-defined table functions. Commits and rollbacks cannot be executed within table UDFs.
• Result sets cannot be returned from table functions.
• Not designed for single invocations.
• Can only be referenced in the FROM clause of a query.
• User-defined external table functions can read SQL data, but cannot modify SQL data. As an alternative SQL table functions can be used to contain SQL statements that modify SQL data.

Common uses

• Encapsulate a complex, but commonly used sub-query.
• Provide a tabular interface to non-relational data. For example a user-defined external table function can read a spreadsheet and produce a table of values that can be directly inserted into a table or directly and immediately accessed within a query.

Supported implementations

• SQL implementation
• External implementation