# 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

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

#### Drop a Function

```sql
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

```sql
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

```sql
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
