15.00 - Rules for Using SQL UDFs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules for Using SQL UDFs

The rules for using SQL UDFs are as follows:

  • SQL UDF names can be up to 128 UNICODE characters in length.
  • For information on object naming, see SQL Fundamentals.

  • 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 aborts the request and 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 manual for further information …

    ABORT

    WHERE

    SQL Data Manipulation Language

    DELETE

    WHERE

    SQL Data Manipulation Language

    INSERT

    VALUES

    SQL Data Manipulation Language

    INSERT … SELECT

  • GROUP BY
  • HAVING
  • ON
  • ORDER BY
  • QUALIFY
  • SAMPLE … WHEN
  • select list
  • WHERE
  • WITH … BY
  • SQL Data Manipulation Language

    MERGE

  • INSERT VALUES
  • ON
  • SELECT
  • UPDATE SET
  • SQL Data Manipulation Language

    ROLLBACK

    WHERE

    SQL Data Manipulation Language

    SELECT

  • GROUP BY
  • HAVING
  • ON
  • ORDER BY
  • QUALIFY
  • SAMPLE … WHEN
  • select list
  • WHERE
  • WITH … BY
  • SQL Data Manipulation Language

    UPDATE

  • SET
  • WHERE
  • SQL Data Manipulation Language

    Wherever valid

    Argument to a method or external UDF.

    SQL Data Definition Language

    Wherever valid

  • CASE (Searched Form
  • CASE (Valued Form)
  • SQL Functions, Operators, Expressions, and Predicates

    Wherever valid

    COALESCE

    SQL Functions, Operators, Expressions, and Predicates

    Wherever valid

    NULLIF

    SQL Functions, Operators, Expressions, and Predicates

    Wherever valid

    Aggregate and Window Aggregate Functions

    SQL Functions, Operators, Expressions, and Predicates

    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

    SQL Functions, Operators, Expressions, and Predicates

  • CSUM
  • MAVG
  • MDIFF
  • MLINREG
  • MSUM
  • PERCENT_RANK
  • QUANTILE
  • RANK
  • ROW_NUMBER
  •  

    Wherever valid

    Logical Predicates

    SQL Functions, Operators, Expressions, and Predicates

  • [NOT] BETWEEN
  • [NOT] IN
  • [NOT] LIKE
  • OVERLAPS
  • Wherever valid

    Attribute Functions

    SQL Functions, Operators, Expressions, and Predicates

  • BYTES
  • CHARACTER_LENGTH
  • CHARACTERS
  • FORMAT
  • OCTET_LENGTH
  • TITLE
  • TYPE
  •  

    Wherever valid

    Hash‑Related Functions

    SQL Functions, Operators, Expressions, and Predicates

  • HASHAMP
  • HASHBAKAMP
  • HASHBUCKET
  • HASHROW
  • Wherever valid

    Arithmetic Operators

    SQL Functions, Operators, Expressions, and Predicates

  • ABS
  • CASE_N
  • EXP
  • LOG
  • LN
  • NULLIFZERO
  • RANGE_N
  • SQRT
  • WIDTH_BUCKET
  • ZEROIFNULL
  • Wherever valid

    Trigonometric Functions

    SQL Functions, Operators, Expressions, and Predicates

  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • COS
  • SIN
  • TAN
  • Wherever valid

    Hyperbolic Trigonometric Functions:

    SQL Functions, Operators, Expressions, and Predicates

  • ACOSH
  • ASINH
  • ATANH
  • COSH
  • SINH
  • TANH
  • Wherever valid

    DateTime Functions

  • ADD_MONTHS
  •  

  • EXTRACT
  • SQL Functions, Operators, Expressions, and Predicates

    Wherever valid

    String Operators and Functions

    SQL Functions, Operators, Expressions, and Predicates

  • ||
  • 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
  • Non‑deterministic functions (such as RANDOM) and non‑deterministic 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 non‑deterministic 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:

  • CREATE CAST (see “CREATE CAST/ REPLACE CAST” on page 212)
  • CREATE ORDERING (see “CREATE ORDERING/ REPLACE ORDERING” on page 416)
  • CREATE TRANSFORM (see “CREATE TRANSFORM/ REPLACE TRANSFORM” on page 694)
  • 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 SQL Data Manipulation Language 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 SQL Data Manipulation Language 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 SQL Data Manipulation Language for details).