User-defined functions

CrateDB supports user-defined functions (UDFs), allowing you to extend SQL functionality by writing custom functions in JavaScript.

UDFs are useful when you need to encapsulate business logic or perform complex calculations that are difficult or inefficient to express using standard SQL expressions.

---

Overview

• UDFs in CrateDB are written in JavaScript.

• They are scalar functions, meaning they return a single value per input row.

• UDFs are stored in the cluster and are available across sessions until explicitly dropped.

---

Syntax

Create a Function

CREATE FUNCTION function_name(arg1_type, arg2_type, ...)
RETURNS return_type
LANGUAGE JAVASCRIPT
AS 'function function_name(arg1, arg2, ...) { ... }';

Drop a Function

DROP FUNCTION function_name(arg1_type, arg2_type, ...);

Note that the function signature (name and argument types) must match exactly.

---

Example

1. Define a UDF

CREATE FUNCTION my_subtract_function(integer, integer)
RETURNS integer
LANGUAGE JAVASCRIPT
AS 'function my_subtract_function(a, b) { return a - b; }';

This creates a function my_subtract_function(a, b) that subtracts two integers.

2. Use the UDF

SELECT doc.my_subtract_function(3, 1) AS col;

Result:

+-----+
| col |
+-----+
|   2 |
+-----+

---

Notes and Limitations

• UDFs are evaluated per row. If performance is critical and the logic can be expressed in SQL, prefer built-in SQL functions.

• UDFs written in JavaScript are sandboxed and cannot access external resources or perform I/O.

• Only JavaScript is currently supported for UDFs. Other languages are not supported at this time.

• You must have the appropriate privileges to create or drop functions.

---

Usage Scenarios

• Encapsulate frequently used expressions (e.g., discount formulas, conversions)

• Implement simple data transformations

• Bridge gaps between SQL features and application-specific logic