Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

CREATE FUNCTION

Creates a new UDF (user-defined function). A UDF can contain either a SQL expression or JavaScript code, and can return either scalar or tabular results.

See also:

ALTER FUNCTION , SHOW USER FUNCTIONS

Syntax

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ LANGUAGE JAVASCRIPT ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Required Parameters

name ( [ arg_name arg_data_type ] [ , ... ] )

Specifies the identifier (and optionally one or more arguments/inputs) for the UDF. The identifier does not need to be unique for the schema in which the UDF is created because UDFs are identified and resolved by their name and argument types.

However, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

RETURNS ...

Specifies the results returned by the UDF, which determines the UDF type:

  • result_data_type: Creates a scalar UDF that returns a single value with the specified data type.

  • TABLE ( col_name col_data_type , ... ): Creates a table UDF that returns tabular results with the specified table column(s) and column type(s).

AS function_definition

Defines the code executed by the UDF. The contents depend on the type of UDF created:

  • SQL UDF: Any valid SQL expression. For more details about SQL UDFs, including examples, see Overview of UDFs.

  • JavaScript UDF: Any valid JavaScript. For more details about JavaScript UDFs, including examples, see JavaScript UDFs.

For more details, see Usage Notes (in this topic).

Optional Parameters

SECURE

Specifies that the function is secure. For more information about secure functions, see Secure UDFs.

LANGUAGE JAVASCRIPT

Specifies that function_definition contains JavaScript code; otherwise, function_definition must contain a SQL expression.

Default: No value (i.e. SQL UDF is created)

CALLED ON NULL INPUT or . RETURNS NULL ON NULL INPUT | STRICT

Specifies the behavior of the UDF when called with null inputs. In contrast to system-defined functions, which always return null when any input is null, UDFs can handle null inputs, returning non-null values even when an input is null:

  • CALLED ON NULL INPUT will always call the UDF with null inputs. It is up to the UDF to handle such values appropriately.

  • RETURNS NULL ON NULL INPUT (or its synonym STRICT) will not call the UDF if any input is null. Instead, a null value will always be returned for that row. Note that the UDF might still return null for non-null inputs.

Default: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Specifies the behavior of the UDF when returning results:

  • VOLATILE: UDF might return different values for different rows, even for the same input (e.g. due to non-determinism and statefullness).

  • IMMUTABLE: UDF assumes that the function, when called with the same inputs, will always return the same result. This guarantee is not checked. Specifying IMMUTABLE for a UDF that returns different values for the same input will result in undefined behavior.

Default: VOLATILE

COMMENT = 'string_literal'

Specifies a comment for the UDF, which is displayed in the DESCRIPTION column in the SHOW FUNCTIONS and SHOW USER FUNCTIONS output.

Default: user-defined function

Usage Notes

  • The delimiters around the function_definition can be either single quotes or a pair of dollar signs. Using $$ as the delimiter makes it easier to write stored procedures that contain single quotes. Some of the examples below use $$ as the delimiter.

  • If the delimiter for the body of the function is the single quote character, then any single quotes within function_definition (e.g. string literals) must be escaped by single quotes.

  • function_definition has size restrictions. The maximum allowable size is subject to change.

  • Snowflake does not validate JavaScript code at UDF creation time (i.e. creation of the UDF succeeds regardless of whether the code is valid). If the code is not valid, errors will be returned when the UDF is called at query time. For more details, see JavaScript UDFs.

  • The optional [ [ NOT ] NULL ] clause indicates whether the function can return NULL values or must return only NON-NULL values. The default is NULL (i.e. the function can return NULL).

    Note

    Currently, the NOT NULL clause is enforced only for JavaScript UDFs, not SQL UDFs. SQL UDFs declared as NOT NULL can return NULL values. Snowflake recommends avoiding NOT NULL for SQL UDFs unless the code in the function is written to ensure that NULL values are never returned.

Examples

Create a simple SQL scalar UDF that returns a hard-coded approximation of the mathematical constant pi:

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

Create a simple SQL table UDF that returns hard-coded values:

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;

SELECT * FROM TABLE(simple_table_function());

Output:

+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

Create a SQL table UDF named get_countries_for_user that returns the results of a query:

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

Create a JavaScript UDF named js_factorial:

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';