Table UDFs and Table Parameterized Functions

Table UDFs are external user-defined C, C++, or Java table functions. Table UDFs, like scalar and aggregate UDFs, can accept scalar input parameters. Unlike scalar and aggregate UDFs, table UDFs produce row sets as output. Table UDFs and TPFs can be referenced wherever a table expression is allowed. For example, SELECT ... FROM <table-expr>. TPFs extend the capabilities of Table UDFs by allowing a row set to be consumed as an input parameter. The TABLE input parameter is not a scalar value, but rather a row set defined by an input query expression.

You declare a Table UDF using the CREATE PROCEDURE statement. Scalar and aggregate UDFs continue to use the CREATE FUNCTION statement as in earlier releases.

A new UDF API is introduced in this release to support table UDFs: EXTFN_V4_API. The EXTFN_V3_API is preserved for backward compatibility for existing scalar and aggregate UDFs.

Table UDFs require the InDatabase Analytics option IQ_IDA license. Existing v3 scalar and v3 aggregate UDFs continue to use the IQ_UDF license.

Table parameterized functions (TPFs) are a extension of table UDFs. Like table UDFs, TPFs produce row sets as output. However, unlike table UDFs, TPFs accept either table or scalar input parameters (table UDFs accept only scalar).

You can configure user-specified partitioning for your TPF. Declare a partitioning scheme that breaks down the dataset into smaller pieces of query processing to distribute across the nodes of the multiplex. This enables you to execute the TPF in parallel in a distributed server environment over partitions of row sets.

See these topics in the Sybase IQ documentation: