15.10 - function_name - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

Calling name for the function.

For information about naming database objects, see SQL Fundamentals, B035-1141.

This is a mandatory attribute for all table UDFs.

If you use function_name to identify the function, take care to follow the identifier naming conventions of the programming language in which it is written.

You cannot give a table UDF the same name as an existing Teradata Database-supplied function (also known as an intrinsic function) unless you enclose the name in QUOTATION MARK characters. For example, “TRIM”(). Using the names of intrinsic functions for UDFs is a poor programming practice and should be avoided.

A UDT and a table UDF without parameters that is stored in SYSUDTLIB cannot have the same name.

If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. Otherwise, Teradata Database returns an error to the requestor. Instead, use a multibyte session character set.

function_name must match the spelling and case of the C, C++, or Java function name exactly if you do not specify a specific_function_name or external_function_name. The same suggestion applies to the naming conventions of the Java language for Java table functions. This rule applies only to the definition of the function, not to calling it.

SQL supports function name overloading within the same function class, so function_name need not be unique within its class; however, you cannot give the same name to both a scalar and an aggregate function within the same database or user.

Parameter data types and number of parameters are used to distinguish among different functions within the same class that have the same function_name.

See SQL External Routine Programming, B035-1147 for further information about function overloading.

Optional database or user name specified if the function is to be created or replaced in a non-default database or user.
If you use the recommended SYSLIB database as your UDF depository, you must modify the size of its permanent space and grant appropriate privileges on it because it is created with 0 permanent space and without access privileges. See the CREATE FUNCTION/REPLACE FUNCTION topic “Function Calling Argument” in the SQL Data Definition Language - Detailed Topics, B035-1184.
Users must have the EXECUTE FUNCTION privilege on any UDF they run from SYSLIB.
If you do not specify a database name, then the system creates or replaces the function within the current database or user.

Example: Creating a Function from Input Arguments

This example takes variable input arguments based on input from a previous correlated subquery in the FROM clause of a SELECT request, that is not shown. The function definition inputs XML text, which it then parses to extract a customer ID, store number, and the set of items purchased. The output produces one row per item purchased, with the columns being based on existing columns in the table defined in the function definition.

    CREATE FUNCTION xml_extract( xml_text VARCHAR(64000))
                     store   INTEGER,
                     item    INTEGER)
    NO SQL

Example: Creating and Using an XML Function

Assume you have the following table function definition:

    CREATE FUNCTION xml_extract( xml_text LOCATOR CLOB)
                    store   INTEGER,
                    item    INTEGER)
     NO SQL

This function extracts all items from the CLOB xml_text that have been ordered from a particular store by a particular customer by means of a web application. It then produces one result row for each item ordered.

The XML data is already in a database table with the following table definition.

    CREATE TABLE xml_tbl (
      store_no INTEGER,
      refnum   INTEGER,
      xml_store_text CLOB)
    UNIQUE PRIMARY INDEX (store_no, refnum);

The assumptions underlie the analysis of the XML data by means of the xml_extract table function.

  • Table xml_tbl contains the following columns.
    • A store number column named store_no.
    • A reference number column named refnum.
    • An XML text column named xml_store_text.
  • The xml_store_text column contains the XML-formatted text for customers who ordered web-based items from the store.
  • Each XML text data column contains information for the customer who placed the order as well as the items that customer ordered.

The purpose of the table function is to extract all items the customer ordered from the XML document. One XML row is created for each order placed by the online web-based system. Because the XML text could consist of several items, a table function is the natural approach to extracting the data, with one row being extracted for each item ordered. If there were 10 items in the XML text, then the table function would return a 10-row table.

The following SELECT request shows a possible application for the xml_extract table function:

     SELECT l.customer_id l.store, l.item,  
     FROM (SELECT xml_store_text
           FROM xml_tbl AS x 
           WHERE store_no = 25), TABLE(xml_extract(x.xml_text_store)) 
                                 AS l (cust_id,store,item);

This SELECT request produces one row for each item bought by all customers from store 25. Its first derived table produces the XML test field from all rows with a store number of 25. The second derived table is the result of evaluating table function xml_extract.

The logical process followed by the SELECT operation is as follows:

  1. Create the first derived table from the first subquery in the FROM clause with the table correlation name x.
  2. Evaluate the table function.

    The function has an input argument that references column x.xml_text_store from the derived table. The database must invoke the table function repeatedly in a loop for each row produced by table x. The loop ends when the table function returns with the “no more data” message.

    The process followed by the loop is as follows:

    1. Read a row for table x where store_no = 25.
    2. Determine whether such a row is found.
      IF a matching row is … THEN …
      found continue processing.
      not found stop processing.
    3. Call the table function xml_extract (x.xml_text_store).
    4. Determine whether there is more data to process.
      IF the call … THEN …
      does not return with a SQLSTATE code of ‘02000’ 
(no more data) continue processing.
      returns with a SQLSTATE code of ‘02000’ go to Stage g.
    5. Write the new row produced by the xml_extract table function.
    6. Go to Stage c.
  3. Return results to requestor.

To elaborate further from yet another perspective, Teradata Database undertakes the following process when solving this problem:

  1. The system passes the x.xml_text_store result produced by the derived table SELECT request as input to the table function xml_extract.
  2. The system invokes the table function repeatedly for the same row.

    Each time the table function is called, it produces one row containing the cust_id, store , and item columns.

  3. When the table function has exhausted everything from that xml_text_store CLOB, it returns with an SQLSTATE of “no data” (‘02000’).
  4. This “no more data” result causes the system to read the next row produced by the first derived table.
  5. The system repeats the process for another customer, generating more rows for the next xml_text_store.

Example: Constant Reference Table Function

This example shows a constant reference table function equijoined to another table.

Note the following things about this example:

  • The table function is sent to all AMPs with the same constant data; therefore, it is a constant mode table function (see “Constant Mode Table Function Body” in SQL External Routine Programming, B035-1147).
  • The table function tudf1 produce all the rows selected.

    After that, the WHERE condition selects a subset from those produced. Only the qualifying rows are returned to the requestor.

           SELECT  *
           FROM tbl_1, TABLE(tudf1(28.8, 109)) AS tf2
           WHERE tbl_1.c1 = tf2.c1;

Example: Variable Reference Table Function

This example shows a variable reference table function equijoined to another table.

Note the following things about this example:

  • The table function is sent to all AMPs and is passed tbl.c1 from all rows in tbl1; therefore it is a variable mode table function (see “Variable Mode Table Function Body” in SQL External Routine Programming, B035-1147).
  • The designer of this table function added a third argument to demonstrate that the function can provide some intelligence by not producing any rows unless the condition “equal” is met. For example, if the input argument tbl_l.c1 and the row for value tf2.c1 are not equal, the function does not produce any rows. This code eliminates the need for a subsequent step to filter rows produced by the table function resulting from the WHERE clause condition.
           SELECT *
           FROM tbl_1, TABLE(tudf1(28.8, tbl_1.c1, 'equal')) AS tf2
           WHERE tbl_1.c1 = tf2.c1;