Function

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with Function:

• Manage the lifecycle of new and existing functions
• Manage grants of a function

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.

Function in Current Configuration

databases:
  <database-name>:
    schemas:
      <schema-name>:
        functions:
          <function-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Function in SOLE for Data Products

databases:
  <database-name>:
    schemas:
      <schema-name>:
        functions:
          <function-name>| unique identifier:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Overloading means you can add multiple functions with the same name but with different arguments. Use a pipe | after the function name. If you add more than two functions, each function's text after the pipe must be unique. See Examples.

Each function must have a unique signature. A signature is generated based on the following parameters:

• Functions FQN (fully qualified name), which is made up of:
  • Database name
  • Schema name
  • External Function name
• Number of arguments
• Data types of arguments
• The sequence of arguments (mainly data type)

The function is treated as duplicated if you have identical values for the above parameters.

caution

Due to a Snowflake limitation, you can't create a UDF and procedure with the same signature in the same schema.

Overloading

- function:
    name: <function-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
return_type	Required	String	Specifies the data type returned by the function
statement	Required	String	Specifies the JavaScript / Java / SQL/ Python code used to create the function
argument	Optional	Object: See here for a definition of argument	List of the arguments for the function
comment	Optional	String	Specifies a comment for the function
deleted	Optional	Boolean: True enables deletion prevention, False does nothing	Specifies what objects are allowed to be deleted
grants	Optional	Map: See Supported Function Grants to Roles	Lists Privileges and Roles to which privileges are granted on the current function
handler	Optional	String	The handler method for Java function
Imports	Optional	List of String	Jar files to import for Java function
language	Optional	String: java, javascript, sql, python	The language of the statement
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the function. See Changing Manage Mode before changing the value.
null_input_behavior	Optional	String	Specifies the behavior of the external function when called with null inputs
packages	Optional	List of String	Jar files to import for Java function
return_behavior	Optional	String	Specifies the behavior of the function when returning results
runtime_version	Optional	String	Required for Python functions. It specifies the Python runtime version in a string format. For example, 3.8 should be specified as runtime_version: "3.8"
target_path	Optional	String	The target path for compiled jar file for Java function

caution

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like argument, return_type, statement, language, etc.

argument parameter

In the arguments parameter, you can specify the arguments for the function.

Multiples arguments in an object format can be listed in the argument parameter with each having the following supported parameters:

• TYPE: Data Type of the Column
  • REQUIRED
  • Configuration key: type
  • Data Type: String

Example

arguments:
  ARG_1:
    type: VARCHAR
  ARG_2:
    type: DATE

Supported function grants to roles

Following are the privileges you can grant to roles in the function definition:

• USAGE
• OWNERSHIP

Examples

JavaScript function

Function in Current Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          JAVASCRIPT_FUNCTION:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR
              ARG_2:
                type: DATE
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|11:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216):
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
              ARG_3:
                type: DATE
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"

Function in SOLE for Data Products

- function:
    name: JAVASCRIPT_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using JavaScript language"
    language: javascript
    return_type: VARCHAR
    return_behavior: IMMUTABLE
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: "return 1"

Java function

Function in Current Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          JAVA_FUNCTION:
            comment: "Function using Java language"
            language: java
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            handler: "CoolFunc.test"
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: 'class CoolFunc {public static String test(int n) {return "hello!";}}'
            arguments:
              ARG1:
                type: NUMBER

Function in SOLE for Data Products

- function:
    name: JAVA_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using Java language"
    language: java
    return_type: VARCHAR
    return_behavior: IMMUTABLE
    handler: "CoolFunc.test"
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: 'class CoolFunc {public static String test(int n) {return "hello!";}}'
    arguments:
      ARG1:
        type: NUMBER

Python function

Function in Current Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          PYTHON_FUNCTION:
            comment: "Function using Python language"
            language: python
            return_type: NUMBER(38,0)
            return_behavior: VOLATILE
            null_input_behavior: "CALLED ON NULL INPUT"
            runtime_version: "3.8"
            handler: "add_py"
            statement: "def add_py(i): return i+1"
            arguments:
              ARG1:
                type: NUMBER

Function in SOLE for Data Products

- function:
    name: PYTHON_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using Python language"
    language: python
    return_type: NUMBER(38,0)
    return_behavior: VOLATILE
    null_input_behavior: "CALLED ON NULL INPUT"
    runtime_version: "3.8"
    handler: "add_py"
    statement: "def add_py(i): return i+1"
    arguments:
      ARG1:
        type: NUMBER

SQL function

Function in Current Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_FUNCTION:
            comment: "Function using SQL language"
            language: sql
            return_type: NUMBER(38,0)
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "a * b"
            arguments:
              A:
                type: NUMBER
              B:
                type: NUMBER

Function in SOLE for Data Products

- function:
    name: SQL_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language"
    language: sql
    return_type: NUMBER(38,0)
    return_behavior: IMMUTABLE
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: "a * b"
    arguments:
      A:
        type: NUMBER
      B:
        type: NUMBER

SQL function that returns a table

Function in Current Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_TABLE_FUNCTION:
            comment: "Function using SQL language that returns table"
            language: sql
            return_type: "TABLE (A NUMBER, B NUMBER)"
            statement: "select 1, 2"

Function in SOLE for Data Products

- function:
    name: SQL_TABLE_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language that returns table"
    language: sql
    return_type: "TABLE (A NUMBER, B NUMBER)"
    statement: "select 1, 2"