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