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