CREATE/REPLACE FUNCTION SQL Statement | Teradata Vantage - 17.10 - CREATE FUNCTION and REPLACE FUNCTION (External Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Compiles and installs an external UDF and creates or replaces the SQL function definition used to invoke that UDF.

Customers using Vantage delivered as-a-service cannot create their own C++ and Java UDFs, UDMs, UDTs, or External Stored Procedures.

ANSI Compliance

CREATE FUNCTION is ANSI SQL:2011-compliant with extensions.

REPLACE FUNCTION is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

The privileges required to perform CREATE FUNCTION and REPLACE FUNCTION differ:
  • You must have explicit CREATE FUNCTION privileges on the database, including SYSUDTLIB for UDFs associated with UDTs, in which the function is to be contained to perform the CREATE FUNCTION request.

    The system does not grant the CREATE FUNCTION privilege automatically when you create a database or user: you must grant that privilege explicitly.

    CREATE FUNCTION is not granted implicitly on the databases and functions owned by a database or user unless the owner also has an explicit WITH GRANT OPTION privilege defined for itself.

  • You must have explicit DROP FUNCTION privileges on the function or on the database in which the function is contained to perform the REPLACE FUNCTION statement on an existing function. You do not need the CREATE FUNCTION privilege to replace a function.
  • You must have explicit CREATE FUNCTION privileges on the database in which the function is to be contained to perform the REPLACE FUNCTION statement to create a new function.
  • If you are not the creator of a function, you must have the EXECUTE FUNCTION privilege on any UDF that you run from the SYSLIB database.

    The creator of a function is granted EXECUTE FUNCTION on that UDF automatically.

If a UDT is specified as an input parameter or the function result, the current user must have one of the following privileges:
  • UDTUSAGE on the SYSUDTLIB database.
  • UDTUSAGE on the specified UDT.

If the function is to be used to enforce the security policy for a table row, but the purpose of the function is not to delete, insert, select, or update table rows, you must have the appropriate OVERRIDE DELETE CONSTRAINT, OVERRIDE INSERT CONSTRAINT, OVERRIDE SELECT CONSTRAINT, or OVERRIDE UPDATE CONSTRAINT privilege to execute it. The request that invokes the function must also include the values to be assigned to the constraint columns of the target table rows.

Privileges Granted Automatically

The following privileges are granted automatically to the creator of an external function:
  • DROP FUNCTION
  • EXECUTE FUNCTION