15.00 - Table UDF - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Table UDF

As user-defined function invoked in the FROM clause of a SELECT statement, a Table UDF returns a table to the statement.

See the TABLE option of the FROM clause in SQL Data Manipulation Language.

ANSI Compliance

Table UDFs are partially ANSI SQL:2011 compliant.

The requirement that parentheses appear when the argument list is empty is a Teradata extension to preserve compatibility with existing applications.

Restrictions

A table UDF can only appear in the FROM clause of an SQL SELECT statement. The SELECT statement containing the table function can appear as a subquery.

Authorization

You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.

To invoke a table UDF that takes a UDT argument or returns a UDT, you must have the UDTUSAGE privilege on the SYSUDTLIB database or on the specified UDT.

Usage Notes

When Teradata Database evaluates a table UDF expression, it invokes the table function which returns a table a row at a time in a loop to the SELECT statement. The function can produce the rows of a table from the input arguments passed to it or by reading an external file or message queue.

A table function can have 128 input parameters. The rules that apply to the arguments in a table function call are the same as those that apply to a scalar function call. See “UDF Arguments” on page 1324.

Table UDFs do not have return values. The columns in the result rows that they produce are returned as output parameters.

The output parameters of a table function are defined by the RETURNS TABLE clause of the CREATE FUNCTION statement. The number of output parameters is limited by the maximum number of columns that can be defined for a regular table.

The number and data types of the output parameters can be specified statically in the CREATE FUNCTION statement or dynamically at runtime in the SELECT statement that invokes the table function.

Table Functions and Table Operators cannot execute against fallback data when an AMP is Down. Once the AMP is returned to service the query can complete as normal.

Example  

In this example, the extract_field table UDF is used to extract the customer ID, store number, and item ID from the pending_data column of the raw_cust table.

The raw_cust table is defined as:

   CREATE SET TABLE raw_cust ,NO FALLBACK ,
      NO BEFORE JOURNAL,
      NO AFTER JOURNAL,
      CHECKSUM = DEFAULT
      (
       region INTEGER,
       pending_data VARCHAR(32000) CHARACTER SET LATIN NOT CASESPECIFIC)
   PRIMARY INDEX (region);
 

The pending_data text field is a string of numbers with the format:

   store number, entries:customer ID, item ID, ...; repeat;

where:

  • store number is the store that sold these items to customers.
  • entries is the number of items that were sold.
  • customer ID, item ID represent the item each customer bought. customer ID, item ID is repeated entries times ending with a semi-colon ';'.
  • The above sequence can be repeated.
  • The following shows sample data from the raw_cust table:

         region  pending_data
       --------  ---------------------------------------------------------
              2  7,2:879,3788,879,4500;08,2:500,9056,390,9004;
              1  25,3:9005,3789,9004,4907,398,9004;36,2:738,9387,738,9550;
              1  25,2:9005,7896,9004,7839;36,1:737,9387;

    The following shows the SQL definition of the extract_field table UDF:

       CREATE FUNCTION extract_field (Text VARCHAR(32000),
                                      From_Store INTEGER)
       RETURNS TABLE (Customer_ID INTEGER,
                      Store_ID INTEGER,
                      Item_ID INTEGER)
       LANGUAGE C
       NO SQL
       PARAMETER STYLE SQL
       EXTERNAL NAME extract_field;

    The following query extracts and displays the customers and the items they bought from store 25 in region 1.

       SELECT DISTINCT cust.Customer_ID, cust.Item_ID
       FROM raw_cust,
       TABLE (extract_field(raw_cust.pending_data, 25))
       AS cust
       WHERE raw_cust.region = 1;

    The output from the SELECT statement is similar to:

       Customer_ID       Item_ID
       ------------  -----------
               9005         3789
               9004         4907
                398         9004
               9005         7896
               9004         7839

    Related Topics

     

    FOR more information on …

    SEE …

    Implementing external UDFs

    SQL External Routine Programming.

  • CREATE FUNCTION
  • REPLACE FUNCTION
  • SQL Data Definition Language.
  • Database Administration.
  • EXECUTE FUNCTION and UDTUSAGE privileges

    SQL Data Control Language.

    the TABLE option in the FROM clause of an SQL SELECT statement

    SQL Data Manipulation Language.