# User-Defined Function collection

**URL:** <https://community.cratedb.com/t/user-defined-function-collection/773>\
**Category:** Tutorials\
**Tags:** sql, fundamentals\
**Created:** [August 9, 2021, 11:52am UTC](https://community.cratedb.com/t/user-defined-function-collection/773 "2021-08-09T11:52:21Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [August 9, 2021, 11:52am UTC](https://community.cratedb.com/t/user-defined-function-collection/773/1 "2021-08-09T11:52:22Z")

</div>

CrateDB supports [User-Defined Functions](https://crate.io/docs/crate/reference/en/latest/general/user-defined-functions.html#user-defined-functions) to extend the functionality provided out of the box with custom functionality.

Feel free to use these UDFs for your own use case or as a starting point for creating your own UDFs

# Array Functions

## Sort numeric arrays

Sorts an integer array ascending:

```sql
CREATE OR REPLACE FUNCTION array_sort_asc(arr ARRAY(INTEGER)) 
RETURNS ARRAY(INTEGER) 
LANGUAGE JAVASCRIPT 
AS 'function array_sort_asc(arr) { 
        return arr.sort((a, b) => a - b); 
    }';                                  
}';

```

Usage:

```sql
SELECT array_sort_asc([200, 1, 8]);
-- Result: [1, 8, 200]

```

## Filter numeric arrays

Filters all elements in an array within a given range:

```sql
CREATE OR REPLACE FUNCTION array_filter(arr ARRAY(INTEGER), min_value INTEGER, max_value INTEGER)
RETURNS ARRAY(INTEGER)
LANGUAGE JAVASCRIPT
AS 'function array_filter(arr, min_value, max_value) { 
        return arr.filter(element => element >= min_value && element <= max_value); 
}';  

```

Usage:

```sql
SELECT array_filter([5, 7, 20], 2, 8) 
-- Result: [5, 7]

```

## Remove an element by index

Remove the element in a given position:

```sql
CREATE OR REPLACE FUNCTION array_remove_index(ARRAY(INTEGER), int)
RETURNS ARRAY(INTEGER)
LANGUAGE JAVASCRIPT
AS 'function array_remove_index(var_array,start) {
    var_array.splice(start,1);
    return var_array;
}';

```

Usage:

```sql
SELECT array_remove_index([200, 1, 8],2);
-- Result: [200, 1]

```

## Remove elements by value

Remove every occurrence of an element with a given value:

```sql
CREATE OR REPLACE FUNCTION array_remove_values(ARRAY(INT), INT)
RETURNS ARRAY(INT)
LANGUAGE JAVASCRIPT
AS 'function array_remove_values(var_array,value) {
    indexof = var_array.indexOf(value);
    while(indexof != -1){
     var_array.splice(indexof,1);
     indexof = var_array.indexOf(value);
    }
    return var_array;
}';

```

Usage:

```sql
SELECT array_remove_values([1, 2, 2, 2, 3],2);
-- Result: [1, 3]

```

# Vector Functions

## Vector distance

Calculates the distance between two two-dimensional vectors:

```sql
CREATE OR REPLACE FUNCTION vector_distance(INTEGER, INTEGER, INTEGER, INTEGER)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS 'function vector_distance(x1, y1, x2, y2) {
  return Math.sqrt(
      Math.pow((x2 - x1), 2) + Math.pow((y2 - y1), 2)
  );
}';

```

Usage:

```sql
SELECT vector_distance(1, 3, 4, 5);
-- Result: 3.6055

```

## Distance between points in an n-dimensional space

Calculates the distance between two points represented as arrays where the length of the arrays is the dimensionality of the space containing the points:

```sql
CREATE OR REPLACE FUNCTION n_dimensional_distance (p1 ARRAY(DOUBLE),p2 ARRAY(DOUBLE))
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS
'	function n_dimensional_distance(p1,p2) {
		sum = 0;
		for (i = 0; i < p1.length; i++) { sum += Math.pow(p2[i] - p1[i], 2); }
		return Math.sqrt(sum);
	}';

```

Usage:

```sql
SELECT n_dimensional_distance([11,22,33],[44,55,66]);
-- Result: 57.15767664977295

```