CREATE FUNCTION

Executes user-provided code in SELECT, INSERT and UPDATE statements. The UDF scope is keyspace-wide. By default, UDF includes support for Java generic methods and Javascript. See User Defined Functions to add support for additional JSR-223 compliant scripting languages, such as Python, Ruby, and Scala.

Synopsis

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] [keyspace_name.]function_name (argument_list [ , ... ])
  ( CALLED | RETURNS NULL ) ON NULL INPUT RETURNS cql_data_type 
  [ DETERMINISTIC ]
  [ MONOTONIC [ ON argument_name ] ]
  LANGUAGE language_name AS 'code_block' ; 

CREATE function_name

Creates a new function and errors if it already exists, use with IF NOT EXITS to suppress error.

OR REPLACE

Creates a new function or overwrites it if one with the same name already exists.

IF NOT EXITS

Suppresses the error message if a function with the same name already exists, and performs no operation.

var_name var_type

The variable name followed by the CQL data type; these arguments are passed from request to the code block for execution. Use of literals or terms and prepared statement placeholders is also supported. Specify multiple arguments in a comma separated list. For example: column text, num int.

CALLED ON NULL INPUT

Executes the user-provided code block even if the input value is null or missing.

RETURNS NULL ON NULL INPUT

Does not execute the user-provided code block on null values; returns null.

RETURNS cql_data_type

Map the expected output from the code block to a compatible CQL data type.

DETERMINISTIC

Specify for functions that always returns the same output for a certain input. For example, toJson() is a deterministic function; now() and currentDate() are not.

Default: false (non-deterministic).

MONOTONIC [ ON argument_name ]

All arguments or the specified argument are monotonic if they are either entirely non-increasing or non-decreasing.

LANGUAGE language_name

Supported types are Java and Javascript. See User Defined Functions to add support for additional JSR-223 compliant scripting languages, such as Python, Ruby, and Scala.

'code_block' | $$ code_block $$

Enclose the code block in single quotes or if the code block contains any special characters enclose it in double dollar signs ($$). The code is wrapped as a function and applied to the target variables.

CAUTION: UDFs are susceptible to all of the normal issues that may occur with the chosen programming language. Safe guard against exceptions, such as null pointer exceptions, illegal arguments, or any other potential sources. An exception during function execution results in the entire statement failing.

Examples

Overwrite or create the fLog function that computes the logarithm of an input value. CALLED ON NULL INPUT ensures that the function will always be executed.

CREATE OR REPLACE FUNCTION cycling.fLog (
  input double
) 
  CALLED ON NULL INPUT 
  RETURNS double LANGUAGE java AS
    'return Double.valueOf(Math.log(input.doubleValue()));'
;

CREATE OR REPLACE FUNCTION cycling.left (
  column TEXT, num int
) 
  RETURNS NULL ON NULL INPUT 
  RETURNS text 
  LANGUAGE javascript AS 
  $$
    column.substring(0, num)
  $$
;

SELECT left(firstname, 1), lastname
FROM cycling.cyclist_name;

 cycling.left(firstname, 1) | lastname
----------------------------+-----------------
                          A |           FRAME
                          M |        MATTHEWS
                          P |       TIRALONGO
                          S |      KRUIKSWIJK
                          A | VAN DER BREGGEN