15.00 - RETURN Statement - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

RETURN Statement

The RETURN statement, which is an SQL control statement similar to the stored procedure control language statements LEAVE, LOOP, WHILE, REPEAT, and so on (see SQL Stored Procedures and Embedded SQL), defines the return expression for an SQL UDF.

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;
     
         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);
     
         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);
     
         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 cannot 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.