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.
- 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.
- Self-referencing
- 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.