16.20 - Rules for Using SQL UDFs - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
The rules for using SQL UDFs are as follows:
  • SQL UDF names are restricted to 128 UNICODE characters in length.

    For information on naming database objects, see Teradata Vantage™ SQL Fundamentals, B035-1141.

  • The maximum number of parameters you can specify for an SQL UDF is 128.
  • Each parameter you specify must have a name associated with it when you create or replace an SQL UDF.
  • A parameter can be assigned any data type supported by Teradata Database except for VARIANT_TYPE or TD_ANYTYPE.
  • You must specify an INLINE clause for an SQL UDF, and the inline specification must be TYPE 1.
  • You must specify a COLLATION clause for an SQL UDF, and the collation specification must be INVOKER.
  • You cannot specify an AS LOCATOR clause for an SQL UDF.

    The AS LOCATOR clause is valid only when used with BLOB or CLOB data types specified as parameters in external UDFs.

  • You cannot specify an explicit parameter mode for SQL UDFs.

    The parameter mode for SQL UDFs always defaults implicitly to IN.

  • SQL UDFs can specify both distinct and structured UDTs as input and return parameters.
  • If you specify a UDT as either an input parameter type or as the function result type, the current user of the function must have either the UDTUSAGE privilege on the SYSUDTLIB database or the UDTUSAGE privilege on the specified UDT.
  • SQL UDFs support the same RETURN types as Teradata Database supports for external UDFs with the exception of the following RETURN types.
    • TABLE
    • TD_ANYTYPE
    • VARIANT_TYPE
  • You cannot specify the FORMAT column attribute for a RETURN expression data type. Otherwise, Teradata Database returns an error to the requestor.
  • You cannot specify the CAST FROM RETURN clause option for SQL UDFs.
  • The LANGUAGE clause of an SQL UDF must specify either LANGUAGE SQL or not be specified (the default value for the LANGUAGE clause of an SQL UDF is SQL).
  • You cannot specify a PARAMETER STYLE clause for an SQL UDF.
  • The SQL DATA ACCESS clause for an SQL UDF must specify CONTAINS SQL.
  • You cannot specify a function CLASS clause for an SQL UDF.
  • You cannot specify an EXTERNAL or EXTERNAL SECURITY clause for an SQL UDF.
  • You can specify an SQL SECURITY clause for an SQL UDF.

    This is the default.

    The only valid option is DEFINER.

  • You must specify an INLINE TYPE clause.

    The only valid option is 1.

  • You must specify a COLLATION clause.

    The only valid option is INVOKER.

  • The only SQL statement you can specify within the definition of an SQL UDF is RETURN.
  • The value returned by the RETURN statement for an SQL UDF must be a scalar value.
  • The SQL expression returned by a RETURN statement cannot contain any explicit table references, nor can it be or contain a scalar subquery.
  • You can invoke an SQL UDF from any of the following SQL statement clauses, functions, expressions, operators, or logical predicates.

    If an item is not listed, it cannot be used to invoke an SQL UDF.

Statement Clause, Function, Expression, or Logical Predicate See this document for further information …
ABORT WHERE Teradata Vantage™ SQL Data Manipulation Language , B035-1146
DELETE WHERE Teradata Vantage™ SQL Data Manipulation Language , B035-1146
INSERT VALUES Teradata Vantage™ SQL Data Manipulation Language , B035-1146
INSERT … SELECT
  • GROUP BY
  • HAVING
  • ON
  • ORDER BY
  • QUALIFY
  • SAMPLE … WHEN
  • select list
  • WHERE
  • WITH … BY
Teradata Vantage™ SQL Data Manipulation Language , B035-1146
MERGE
  • INSERT VALUES
  • ON
  • SELECT
  • UPDATE SET
Teradata Vantage™ SQL Data Manipulation Language , B035-1146
ROLLBACK WHERE Teradata Vantage™ SQL Data Manipulation Language , B035-1146
SELECT
  • GROUP BY
  • HAVING
  • ON
  • ORDER BY
  • QUALIFY
  • SAMPLE … WHEN
  • select list
  • WHERE
  • WITH … BY
Teradata Vantage™ SQL Data Manipulation Language , B035-1146
UPDATE
  • SET
  • WHERE
Teradata Vantage™ SQL Data Manipulation Language , B035-1146
Wherever valid Argument to a method or external UDF. Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144
Wherever valid
  • CASE (Searched Form)
  • CASE (Valued Form)
Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
Wherever valid COALESCE Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
Wherever valid NULLIF Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
Wherever valid Aggregate and Window Aggregate Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
Wherever valid
  • AVG
  • CORR
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • GROUPING
  • KURTOSIS
  • MAX
  • MIN
  • REGR_AVGX
  • REGR_AVGY
  • REGR_COUNT
  • REGR_INTERCEPT
  • REGR_R2
  • REGR_SLOPE
  • REGR_SXX
  • REGR_SXY
  • REGR_SYY
  • SKEW
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM
  • VARPOP
  • VAR_SAMP
Wherever valid Ordered Analytic Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • CSUM
  • MAVG
  • MDIFF
  • MLINREG
  • MSUM
  • PERCENT_RANK
  • QUANTILE
  • RANK
  • ROW_NUMBER
Wherever valid Logical Predicates Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • [NOT] BETWEEN
  • [NOT] IN
  • [NOT] LIKE
  • OVERLAPS
Wherever valid Attribute Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • BYTES
  • CHARACTER_LENGTH
  • CHARACTERS
  • FORMAT
  • OCTET_LENGTH
  • TITLE
  • TYPE
Wherever valid Hash-Related Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • HASHAMP
  • HASHBAKAMP
  • HASHBUCKET
  • HASHROW
Wherever valid Arithmetic Operators Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • ABS
  • CASE_N
  • EXP
  • LOG
  • LN
  • NULLIFZERO
  • RANGE_N
  • SQRT
  • WIDTH_BUCKET
  • ZEROIFNULL
Wherever valid Trigonometric Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • COS
  • SIN
  • TAN
Wherever valid Hyperbolic Trigonometric Functions: Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • ACOSH
  • ASINH
  • ATANH
  • COSH
  • SINH
  • TANH
Wherever valid DateTime Functions
  • ADD_MONTHS
  • EXTRACT
Teradata Vantage™ SQL Date and Time Functions and Expressions, B035-1211
Wherever valid String Operators and Functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • ||
  • CHAR2HEXINT
  • COLUMN_NAME
  • DATABASE_NAME
  • INDEX
  • LOWER
  • POSITION
  • SOUNDEX
  • STRING_CS
  • SUBSTRING
  • TABLE_NAME
  • TRANSLATE
  • TRANSLATE_CHK
  • TRIM
  • VARGRAPHIC
  • An SQL UDF routine body cannot contain references to join indexes, macros, triggers, or stored procedures.
  • Unlike the case for external UDFs, an ALTER FUNCTION request cannot be used with an SQL UDF.
  • SQL expressions of any form are valid as arguments for an SQL UDF as long as the SQL expressions are none of the following:
    • Scalar subqueries
    • Boolean expressions
    • Nondeterministic functions (such as RANDOM) and nondeterministic UDFs when the corresponding parameter is specified more than once in the SQL UDF definition.

    This differs from external UDFs, where there is no restriction on nondeterministic functions specified in SQL expressions that are passed as arguments to an external UDF.

  • The data type of SQL any expression passed as an argument should match the data type specified for its corresponding parameter in the function definition.

    If the types do not match, then the they must be of the same class, and the data type of the argument passed by the invoker should be less than the size of the data type of the parameter specified in the UDF; otherwise, the request aborts and returns a function not found error to the requestor.

  • Any arguments passed to an SQL UDF must be passed using the positional parameter method.
  • An SQL UDF can be passed as an argument to another SQL UDF, external UDF, or method.
  • An SQL UDF cannot be referenced by the partitioning expression for the primary index of a table, nor can it be referenced by any CHECK constraints declared for a table definition.

    This differs from the case for external UDFs, which can be referenced by CHECK the constraints declared for a table definition.

  • You cannot invoke an SQL UDF within the definition of a join index.
  • You can invoke an SQL UDF within the definition of a trigger.
  • You can invoke an SQL UDF within the definition of both external and SQL procedures.
  • You can pass an SQL UDF as an argument to an external procedure, an SQL procedure, or a macro.
  • You cannot reference an SQL UDF as an ordering, cast, or transform function in a UDT.

    This contrasts with the case for an external UDF, which can be referenced inside the definition of a UDT.

    You cannot invoke an SQL UDF from any of the following DDL statements:

  • You cannot trace an SQL UDF using a SET SESSION FUNCTION TRACE request.
  • Apart from UDTs, there is no object dependency support in SQL UDFs, which means that if an SQL UDF is referenced inside another database object such as a view or macro, dropping the UDF invalidates the macro or view.

    Teradata Database does not make a dependency check before dropping an SQL UDF. The same is true if a database object such as a UDF that is referenced inside an SQL UDF is dropped, which invalidates the SQL UDF.

    However, you cannot drop a UDT that is referenced inside a SQL UDF definition unless the SQL UDF definition is altered in such a way that the dropped UDT is no longer referenced, or if you drop the SQL UDF referencing the UDT.

  • Self-referencing, forward referencing, and circular referencing by SQL UDFs is not valid.
  • You can invoke an SQL UDF within a derived table. The rules for doing this are the same as the rules for invoking an SQL UDF within a SELECT request (see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 for details).
  • You can invoke an SQL UDF within a view definition. The rules for doing this are the same as the rules for invoking an SQL UDF within a SELECT request (see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 for details).
  • An SQL UDF can be invoked in the WITH RECURSIVE request modifier of a recursive query. The rules for doing this are the same as the rules for invoking an SQL UDF within a SELECT request (see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 for details).