17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

When Vantage 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.

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:entry[;...]
entry
customer_ID,item_ID
store_number
Number that identifies the store that sold the items to the customers.
entries
Number of items sold.
customer_ID
Customer identifier.
item_ID
Item identifier.

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