Example: Referencing an External UDF - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.