Table UDFs and TPFs

Table UDFs are external user-defined C, C++, or Java table functions. Unlike scalar and aggregate UDFs, table UDFs produce row sets as output. SQL queries consume a table UDF's output sets as table expressions.

Scalar and aggregate UDFs can use either the v3 or v4 API, but table UDFs can use only v4.

Declare a table UDF SQL function using the CREATE PROCEDURE statement. Scalar and aggregate UDFs use the CREATE FUNCTION statement.

Table parameterized functions (TPFs) are enhanced table UDFs that accept either scalar values or row sets as input.

• User Roles
  Two types of users work with table UDFs: UDF developers, and SQL analysts.
• Learning Roadmap for Table UDF Developers
  Use annotated examples to learn how to develop a C or C++ table UDF. After completing the development tasks, the SQL analyst can then reference your UDF in a SQL query.
• Learning Roadmap for SQL Analysts
  Reference a C or C++ table UDF in your SQL query.
• Table UDF Restrictions
  Table UDFs and TPFs have some restrictions.
• Get Started
  Familiarize yourself with sample files, concepts, and restrictions before developing table UDFs and TPFs.
• Developing a Table UDF
  The general steps for developing a table UDF include determining input and output, declaring the v4 library, defining the a_v4_extfn_proc descriptor, defining a library entry point function, defining how the server gets row information, implementing the a_v4_extfn_proc structure functions, and implement the a_v4_extfn_table_func structure functions.
• Query Processing States
  The SQL statement that references a UDF goes through query processing states in the SAP Sybase IQ server. In each of these states, the server uses the v4 API to communicate and negotiate with the UDF.
• Row Block Data Exchange
  A row block is the data transfer area between the producer and the consumer.
• Row Block Allocation
  Row block allocation is required when a producer produces data using the fetch_block method or when the consumer uses the fetch_into method to retrieve data.
• Table UDF Query Plan Objects
  The table UDF values and TPF values visible in the query plan.
• Enabling Memory Tracking
  Enable memory tracking to help you locate memory leaks in your UDFs, and to free the leaked memory. Memory tracking imposes a performance penalty.
• Table Parameterized Functions
  A Table parameterized function (TPF) is an extension of a table UDF that accepts table input parameters in addition to scalar input parameters.
• SQL Reference for Table UDF and TPF Queries
  SQL statement reference for queries referencing table UDFs and TPFs.