15.10 - Function - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K
function_name
The function name for the UDF whose most recent SQL create text is to be reported.
data_type
UDT_name
The data type parameters, including UDTs, that uniquely identify an overloaded function name. For a list of data types, see Data Types Syntax.

Example: SHOW FUNCTION (External Form)

This example reports the current DDL and function body for the specific external function name addnum.

     SHOW FUNCTION addnum;
      *** Text of DDL statement returned. 
      *** Total elapsed time was 1 second.
     ------------------------------------------------------------
     REPLACE FUNCTION rgs.add_num (p1 INTEGER, p2 FLOAT)
      RETURNS FLOAT 
      SPECIFIC add_num 
      LANGUAGE C 
      NO SQL 
      PARAMETER STYLE SQL 
      NOT DETERMINISTIC 
      CALLED ON NULL INPUT 
      EXTERNAL NAME 'cs!first1!udftest/first1.c!F!first1' 
      *** Text of DDL statement returned. 
     #define SQL_TEXT Latin_Text
     #include <sqltypes_td.h>
     /* add integer and float */
     void first1(INTEGER  *a,
                 FLOAT    *b,
                 FLOAT    *result,
                 INT      *indc_a,
                 INT      *indc_b,
                 INT      *indc_result,
                 CHAR     sqlstate[6],
                 SQL_TEXT extname[129],
                 SQL_TEXT specific_name[129],
                 SQL_TEXT error_message[257])
        {
        if (*indc_a == -1 || *indc_b == -1)
        {
            *indc_result = -1;
            return;
        }
        *result = *a + *b;
        *indc_result = 0;
        }

Example: SHOW FUNCTION (SQL Form)

SHOW FUNCTION (SQL Form) requests display the DDL create text for the SQL UDF. The output displayed by a SHOW FUNCTION request returns the actual create text that was used to create the SQL UDF and does not return the default values for some of the optional clauses that were not specified explicitly during the creation of the SQL UDF.

     SHOW SPECIFIC FUNCTION udf_1;
     CREATE FUNCTION udf_1 (a INTEGER, 
                            b INTEGER)
     RETURNS INTEGER
     CONTAINS SQL
     RETURN a + b;

Note that the output of this example does not display the default values for some options like the LANGUAGE clause, DETERMINISTIC clause, the null call clause, and so on because the creator of udf1 did not type these clauses explicitly when she created the SQL UDF, so the SHOW FUNCTION request does not return the default values for those clauses.

In the following example, the SHOW FUNCTION request does return the default values for the LANGUAGE clause, the DETERMINISTIC clause, and the null call clause because the creator typed those clauses explicitly when he created udf_2.

     SHOW FUNCTION udf2 (INTEGER, INTEGER);
     CREATE FUNCTION udf2 (a INTEGER, b INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     CALLED ON NULL INPUT
     RETURN a - b;