Example: Referencing an External UDF

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

In this example, the SQL UDF named MySQLUDF references an external UDF named MyExtUDF in the RETURN statement.

Consider the following function definition and query:

   CREATE FUNCTION Test.MySQLUDF (a INT, b INT, c INT)
   RETURNS INT
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN a + b * MyExtUDF(a, b) - c;
   SELECT Test.MySQLUDF(t1.a1, t2.a2, t3.a3) FROM t1, t2, t3;

The user executing the SELECT statement must have the following privileges:

  • SELECT privilege on tables t1, t2, and t3, their containing databases, or on the columns t1.a1, t2.a2, and t3.a3.
  • EXECUTE FUNCTION privilege on MySQLUDF or on the database named Test.

Because the SQL UDF references MyExtUDF, the following privileges are also checked:

  • The creator of MySQLUDF must exist and have the EXECUTE FUNCTION privilege on MyExtUDF or its containing database.
  • The database named Test (the immediate owner of MySQLUDF) must have the EXECUTE FUNCTION privilege on MyExtUDF or its containing database.