15.10 - SPECIFIC specific_function_name - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

Specific name for the function.

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.

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

This clause is mandatory for overloaded function_name s, but is otherwise optional and can only be specified once per function definition.

If you use specific_function_name to identify the C or C++ function name, take care to follow the identifier naming conventions of the C or C++ languages. The same suggestion applies to the naming conventions of the Java language for Java functions.

The specific_function_name must be unique within its database or user to avoid name clashes, unlike function_name. See “function_name” on page 4.

This name is stored in DBC.TVM as the name of the UDF database object.

Optional database or user name.

Example: Ordering Input Parameters to a Table Function

The following simple example illustrates the use of the HASH BY and LOCAL ORDER BY clauses for a table UDF. Table function add2int takes two integer values as input and returns both of them and their sum.

Query Q1 selects all columns from add2int, which requests its input, dt, to be hashed by dt.y1 and value-ordered on each AMP by dt.x1. Note that the specified hashing and local ordering might not be relevant to the add2int function and is only used for illustration.

The expected outcome of Q1 is that dt is first spooled and then hashed by y1 among the AMPs. On each AMP, the rows are sorted by the value of x1. The final hashed and sorted spool is used as the input to add2int.

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER);
     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER);
     REPLACE FUNCTION add2int (
       a INTEGER, 
       b INTEGER)
       addend1 INTEGER, 
       addend2 INTEGER, 
       mysum INTEGER)
     SPECIFIC add2int  
     NO SQL  
     EXTERNAL NAME 'CS!add3int!add2int.c';
/* Query Q1 */
     WITH dt(x1,y1) AS (SELECT a1,b1
                        FROM t1)
     SELECT *  
     FROM TABLE (add2int(dt.x1,dt.y1) 
     HASH BY y1 
     LOCAL ORDER BY x1) AS tf;