Creating a UDF¶

You create a user-defined function (UDF) or user-defined table function (UDTF) with a CREATE FUNCTION command that specifies the function’s properties, including the handler to use for its logic.

This topic lists the steps to create a UDF. For examples, see the CREATE FUNCTION reference.

You create a UDF with the following steps:

1. Write handler code that executes when the UDF is called.

   You can use one of the supported handler languages. For more information, see Supported languages.

2. Choose whether you’ll keep the handler code in-line with the CREATE FUNCTION SQL statement or refer to it on a stage.

   Each has its advantages. For more information, see Keeping handler code in-line or on a stage.

3. Execute a CREATE FUNCTION statement in SQL, specifying properties of the function.

   Code in the following example creates a UDF called function_name with the in-line handler HandlerClass.handlerMethod.

   create function function_name(x integer, y integer)
     returns integer
     language java
     handler='HandlerClass.handlerMethod'
     target_path='@~/HandlerCode.jar'
     as
     $$
         class HandlerClass {
             public static int handlerMethod(int x, int y) {
               return x + y;
             }
         }
     $$;
   

   Copy

   The following describes some of the properties required or typically used when creating a function.

   • Function name.

     The UDF name does not need to match the name of the handler. The CREATE FUNCTION statement associates the UDF name with the handler.

     For more about name constraints and conventions, see Naming and overloading procedures and UDFs.

   • Function arguments, if any.

     See Defining arguments for UDFs and stored procedures.

   • Return type with the RETURNS clause.

     For a scalar return value, the RETURNS clause will specify a single return type; for a tabular return value, RETURNS will specify the TABLE keyword specifying column type in the tabular return value.

     For information about how Snowflake maps SQL data types to handler data types, see Naming and overloading procedures and UDFs.

   • Handler name with the HANDLER clause.

     When required, this is the name of the class or method containing code that executes when the UDF is called. You need specify a handler name only for handlers written in Java and Python. For JavaScript and SQL handlers, all code specified in-line will be executed as the handler.

     The following table describes the form of the HANDLER clause’s value based on the handler language and function type.

     Handler Language	UDF	UDTF
     Java	Class and method name. For example: MyClass.myMethod	Class name only. Handler method name is predetermined by the required interface.
     JavaScript	None.	None.
     Python	Class and method name if a class is used; otherwise, function name. For example: module.my_function or my_function	Class name only. Handler method name is predetermined by the required interface.
     SQL	None.	None.

   • Dependencies required by the handler, if any, using the IMPORTS or PACKAGES clauses.

     For more about making dependencies available to your handler, see Making dependencies available to your code.

   • Handler language runtime with RUNTIME_VERSION clause.

     When the handler language is Java or Python, use the RUNTIME_VERSION clause to specify which supported runtime version to use. Omitting the clause will prompt Snowflake to use the default, which may change in the future.