Relationship of UDFs, Methods, and External Procedures | VantageCloud Lake - General Usage Guidelines for CREATE/REPLACE FUNCTION (SQL Form) - Teradata Vantage

Relationship of SQL UDFs, External UDFs, Table UDFs, Methods, and External Procedures

External UDFs, table UDFs, methods, and external procedures are specific variations of each other and share most properties in common. The generic term used to describe all these is external routine.

You invoke SQL UDFs like you invoke external and table UDFs, but they are written in SQL, so they do not have external elements.

Avoiding Name Clashes among UDFs

• The following column pair must be unique within the DBC.TVM table: DatabaseID, TVMNameI
• The signature of the following routine must be unique:

  database_name.routine_name(parameter_list)

UDFs and methods can have the same SQL names if their SPECIFIC names and associated routine signatures are different.

Protected and Unprotected Execution Modes in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)

SQL UDFs do not have execution modes.

How SQL UDFs Handle SQL Result Codes

The ANSI SQL:2011 standard defines a return code variable named SQLSTATE to accept status code messages. All condition messages are returned to this variable in a standard ASCII character string format.

All SQLSTATE messages are 5 characters in length. The first 2 characters define the message class and the last 3 characters define the message subclass.

For example, consider the SQLSTATE return code ‘22021’. The class of this message, 22, indicates a data exception condition. Its subclass, 021, indicates that a character not in the defined repertoire was encountered.

A SQL warning does not stop a request. A SQL exception does stop a request.

Make sure your UDFs return valid SQLSTATE codes. Vantage does not map SQLSTATE values returned by SQL UDFs to their equivalent SQLCODE values. The following table shows how SQLSTATE codes generated by SQL UDFs are mapped to system messages.

See Return Codes for more information about SQL exception and warning codes.

See Result Code Variables for more information about SQLSTATE and SQLCODE result codes.

Differences between CREATE FUNCTION and REPLACE FUNCTION for a UDF

If you specify CREATE, the function must not exist.

If you specify REPLACE, you can create a new function or replace an existing function with the following restriction: If the function to be replaced was created with a specific function name, you must use that function name in the REPLACE FUNCTION statement.

With REPLACE FUNCTION, you do not need to grant the EXECUTE privilege again to users who already had it.

SQL UDFs and Embedded SQL

You can invoke an SQL UDF from embedded SQL requests as you invoke an SQL UDF from an interactive SQL request. You can invoke SQL UDFs from an embedded SQL request without restrictions.

The following example shows one possible way to invoke an SQL UDF, common_value_expression, from an embedded SQL request.

EXEC SQL FOR 19
  INSERT INTO t1
  VALUES (:var1, df2.common_value_expression(:var2, 2), :var3);

SQL UDFs and SQL Cursors

You can invoke SQL UDFs from within an SQL cursor. The rules for invoking an SQL UDF from within a cursor are the same as those for any other expression specified within an SQL cursor (see Cursors and Embedded SQL for details).

This example invokes an SQL UDF in the WHERE clause of a SELECT request in a cursor.

DECLARE ex1 CURSOR FOR
  SELECT *
  FROM t1
  WHERE df2.common_value_expression(t1.a1, t1.b1) > 1
  ORDER BY t1.a1;

This example invokes an SQL UDF in an UPDATE request in a cursor.

DECLARE ex3 CURSOR FOR
  UPDATE t1 SET b1 = df2.common_value_expression(t1.a1, t1.b1)
  WHERE c1 > 10;

Passing Parameters to an SQL UDF

When you pass parameters directly to a UDF, Vantage returns parameter metadata in a Statement Info parcel (see Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 for details). The rules for passing parameters to an external UDF or method also apply to SQL UDFs (see Rules for Using SQL UDFs and "Function Calling Argument in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)" in Using Functions with CREATE/REPLACE FUNCTION (SQL Form).

In this example, the SQL UDF is not overloaded, so Vantage can resolve the UDF with a ? parameter passed directly to the UDF. Vantage returns the parameter metadata regarding the UDF parameter in the target position.

SELECT myudf(1, ?)
FROM t1;

In this example, the SQL UDF is overloaded, so you must explicitly cast the ? parameter to an acceptable value to enable the UDF to be resolved in Prepare mode. If you do not specify such a cast, Vantage cannot resolve the UDF, so the request aborts and the system returns an error to the requestor. For the purposes of parameter metadata, the data type field of the returned metadata is the result type of the cast.

SELECT myudf(1, CAST(? AS INTEGER), col3)
FROM t1;

SQL User-Defined Functions and Large Objects

The usage characteristics for an SQL UDF with large object parameters or a large object return value are the same as the usage characteristics for any other UDF. You can specify a UDF that accepts a LOB value as an argument in any context in which a UDF is otherwise allowed. You can also use an SQL UDF that returns a LOB value in any context in which a value of that type is appropriate and a UDF is otherwise allowed.

As with other functions and operators, Vantage can apply automatic type conversions to the arguments or the return values of an SQL UDF. Be careful about the possible performance implications of automatic type conversions with large objects used as UDF parameters.

For example, a function whose formal parameter is a BLOB type can be passed a VARBYTE column as the actual argument. Vantage converts the VARBYTE value into a temporary BLOB and then passes that to the SQL UDF. Because even a temporary BLOB is stored on disk, the performance cost of the conversion is significant. To avoid this, consider creating an overloaded function that explicitly accepts a VARBYTE argument.

Another possible cause of undesired conversions is truncation. Declared length is part of the data type specification.

Restriction on Using an SQL Function to Enforce Row-Level Security

You cannot use an SQL function to enforce row-level security for a security constraint.