RETURN Statement in SQL UDFs | CREATE/REPLACE FUNCTION | Teradata Vantage - RETURN Statement - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

The RETURN statement, which is an SQL control statement similar to the SQL procedure control language statements LEAVE, LOOP, WHILE, REPEAT, and so on, defines the return expression for an SQL UDF. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.

The following set of rules applies to coding the RETURN statement for an SQL UDF.
  • You cannot specify more than one RETURN statement per SQL UDF.
  • The SQL expression in the RETURN statement must not contain any references to tables, nor can it contain scalar subqueries.
  • The SQL expression in the RETURN statement can specify references to parameters, constants, SQL UDFs, external UDFs, and methods.

    It cannot specify a FORMAT attribute.

  • If you specify a reference to an SQL UDF in the RETURN statement, it cannot be any of the following types of reference:
    • Self-referencing

      A self-referencing SQL UDF references itself within its own definition.

      The following self-referencing SQL UDF is not valid. The portion of the RETURN expression that causes the error is highlighted in boldface type.

           CREATE FUNCTION df2.myudf1 (a INTEGER, b INTEGER)
           RETURNS FLOAT
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           SPECIFIC df2.myudf1
           CALLED ON NULL INPUT
           SQL SECURITY DEFINER
           COLLATION INVOKER
           INLINE TYPE 1
           RETURN CASE WHEN a < 1
                       THEN  myudf1  (a + 1 + b)
                       ELSE a + b
                  END;

      Result:

           Failure 3807 Object 'myudf1' does not exist.
    • Circular referencing

      A circular referencing SQL UDF refers to a second SQL UDF, which in turn refers back to the first SQL UDF.

      Neither of the following circular referencing SQL UDFs is valid. The portions of the RETURN expressions that cause the errors are highlighted in boldface type.

           CREATE FUNCTION df2.myudf2 (a INTEGER, b INTEGER)
           RETURNS FLOAT
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           SPECIFIC df2.myudf2
           CALLED ON NULL INPUT
           SQL SECURITY DEFINER
           COLLATION INVOKER
           INLINE TYPE 1
           RETURN a + b *  myudf1  (a, b-1);

      Result:

           Failure 3807 Object 'myudf1' does not exist.
           CREATE FUNCTION df2.myudf1 (a INTEGER, b INTEGER)
           RETURNS FLOAT
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           SPECIFIC df2.myudf1
           CALLED ON NULL INPUT
           SQL SECURITY DEFINER
           COLLATION INVOKER
           INLINE TYPE 1
           RETURN a + b *  myudf2  (a, b-1);

      Result:

           Failure 3807 Object 'myudf2' does not exist.
  • The SQL expression in the RETURN statement must not be a conditional expression whose return type is Boolean because Boolean return types are not supported for SQL UDFs.
  • The SQL expression in the RETURN statement can contain any form of arithmetic, string, DateTime functions, and operators that define a scalar result.
  • The SQL expression in the RETURN statement can not contain aggregate or OLAP function.
  • The return expression data type either must match the data type specified in the RETURNS clause of the UDF definition or it must be possible to implicitly cast the return expression to the data type specified in the RETURNS clause; otherwise, the system returns an error to the requestor.