Source: https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/functions/user-defined/
Documentation version: v5.3.2

# User-defined functions (UDFs)

Create and call user-defined functions (UDFs) written in GQL to reuse query logic in NebulaGraph Database Developer Edition.

## Overview

You can define custom functions using GQL expressions, extending the built-in function library of NebulaGraph Database Developer Edition. UDFs can be called in queries just like built-in functions, which simplifies the reuse of complex calculation logic.

## Limitations

- Only functions of type `scalar` and `aggregate` can be defined. For information about function types, see [SHOW FUNCTIONS](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-admin/show/show-functions/).
- Subquery expressions are not supported.
- Variadic parameters are not supported.
- Calling any UDF within a function definition is not supported.
- Global aggregator value variables (such as `@nodeAgg`) are not allowed.

Note

An operation involving a non-default schema or an object in it requires access to the schema, either implicitly or through an explicit `USAGE` grant, as well as the privilege required for the operation. The schema access check and the privilege check for the requested operation are independent; satisfying one does not satisfy the other. Schema access control does not apply to temporary graphs or built-in procedures and functions. For more information, see [Schema access control](https://dev-docs.nebula-graph.io/v5.3.2/server-admin/authorization/schema-access-control/).

## Prerequisites

- To create a UDF, you must be granted the `CREATE FUNCTION` action on the current schema. For more information, see [Roles and privileges](https://dev-docs.nebula-graph.io/v5.3.2/server-admin/authorization/).
- To delete a UDF, you must either be the owner of the UDF, be granted the `DROP` action on the UDF, or be granted the `DROP ANY FUNCTION` action on the current schema. For more information, see [Roles and privileges](https://dev-docs.nebula-graph.io/v5.3.2/server-admin/authorization/).

## Syntax

### Create a UDF

diagramsource

 create_function_statement parameter_declaration  

````
create_function_statement ::=
    'CREATE' 'FUNCTION' ('IF NOT EXISTS')? function_name '(' (parameter_declaration (',' parameter_declaration)* )? ')' 'RETURNS' return_type 'AS' '{'  expression '}' ('COMMENT' string_literal)?

parameter_declaration ::=
    parameter_name parameter_type

````

#### `IF NOT EXISTS`

This clause checks whether a UDF with the same name already exists. If it does, the UDF is not created and no exception is thrown.

If you do not specify this clause, an exception is thrown if a UDF with the same name already exists.

#### `function_name`

The name of the UDF to be created. The name must be a valid identifier and must not conflict with any built-in or user-defined function names. For information about identifiers, see [Rules for naming identifiers](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/overview/keywords-identifiers/).

Unlike UDPs, UDFs do not support [overloading](https://en.wikipedia.org/wiki/Function_overloading). You cannot create multiple UDFs with the same name but different input parameters.

#### `parameter_declaration`

The input parameters of the UDF. Each parameter is defined by a name and a data type.

#### `parameter_name`

The name of the input parameter accepted by the UDF. The name must be unique within the UDF.

#### `parameter_type`

The type of the input parameter accepted by the UDF. It can be any data type. For information about data types, see [Data types](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/).

#### `return_type`

The data type of value returned by the UDF.

#### `COMMENT`

The optional description of the UDF. Use a string literal to help explain the purpose of the function.

#### `expression`

The expression that defines the logic of the UDF.

### Drop a UDF

diagramsource

 drop_function_statement 

````
drop_function_statement ::=
'DROP' 'FUNCTION' 'IF EXISTS'? function_name

````

#### `IF EXISTS`

This clause checks whether the UDF to be deleted exists. If it does not, the UDF is not deleted and no exception is thrown. 

If you do not specify this clause, an exception is thrown if the UDF does not exist.

#### `function_name`

The name of the function to be dropped.

### Show UDF information

diagramsource

 show_functions_statement show_specific_function_statement 

````
show_functions_statement ::= 
    'SHOW' 'GQL'? 'USER DEFINED' 'FUNCTIONS' 
show_specific_function_statement ::= 
    'SHOW' 'GQL'? 'USER DEFINED' 'FUNCTION' function_name

````

For more information, see [SHOW USER DEFINED FUNCTIONS](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-admin/show/show-user-defined-functions/).

## Examples

### Create a UDF without parameters

````
'''
-- Create a function that returns the value of pi:
CREATE FUNCTION IF NOT EXISTS _pi() RETURNS FLOAT AS {3.1415926}
'''

'''
-- Call the function
RETURN _pi() AS pi_value
'''

````

Return:

````
+-----------+
| pi_value  |
+-----------+
| 3.1415926 |
+-----------+

````

### Create a UDF with parameters

````
'''
-- Create a function that calculates the sum of two numbers
CREATE FUNCTION IF NOT EXISTS foo(a INT, b FLOAT) RETURNS FLOAT AS {a + b}
'''

'''
-- Call the function
RETURN foo(10, 5.5) AS result
'''

````

Return:

````
+--------+
| result |
+--------+
| 15.5   |
+--------+

````

### Create a UDF with a comment

````
'''
-- Create a function with a comment
CREATE FUNCTION IF NOT EXISTS add_tax(price FLOAT) RETURNS FLOAT AS {price * 1.06} COMMENT 'Calculates the price including tax' 
'''

'''
-- View the function comment
SHOW USER DEFINED FUNCTION add_tax
'''

````

Return:

````
+---------+------------------------------------------------+------------------------------------+
| name    | signature                                      | comment                            |
+---------+------------------------------------------------+------------------------------------+
| add_tax | add_tax(price FLOAT) -> FLOAT { price * 1.06 } | Calculates the price including tax |
+---------+------------------------------------------------+------------------------------------+

````

### Create a UDF with aggregate functions

````
'''
-- Use built-in aggregate functions in UDFs
CREATE FUNCTION IF NOT EXISTS avg_sum(b FLOAT) RETURNS FLOAT AS {avg(b) + sum(abs(b))}
'''

'''
-- Call the function
FOR pair IN [[1,2], [1,3], [2,3], [4,2]]
RETURN pair[0] AS a, pair[1] AS b 
NEXT
RETURN a, avg_sum(b) AS result GROUP BY a
'''

````

Return:

````
+---+--------+
| a | result |
+---+--------+
| 1 | 7.5    |
| 2 | 6.0    |
| 4 | 4.0    |
+---+--------+

````

### Create a UDF with a session variable

UDFs can reference session variables:

````
'''
-- Set a session variable
SESSION SET VALUE $offset = 10
'''

'''
-- Create a function that uses the session variable
CREATE FUNCTION IF NOT EXISTS add_offset(x FLOAT) RETURNS FLOAT AS {x + $offset}
'''

'''
-- Call the function
RETURN add_offset(5) AS result
'''

````

Return:

````
+--------+
| result |
+--------+
| 15     |
+--------+

````

### Use UDFs in complex queries

Call the UDFs `foo()` and `_pi()` created in the preceding examples in queries:

Example 1:

````
'''
FOR i IN [1, 2, 3, 4]
RETURN i, foo(-i, _pi()) AS calculated_value
ORDER BY foo(-i, _pi()) DESC
'''

````

Return:

````
+---+------------------+
| i | calculated_value |
+---+------------------+
| 1 | 2.1415926        |
| 2 | 1.1415926        |
| 3 | 0.1415926        |
| 4 | -0.8584074       |
+---+------------------+

````

Example 2:

````
'''
USE movie 
MATCH (v:Person WHERE v.id = foo(5, 10.0))-[e]->(n)
RETURN v.id AS vid, n.id AS nid, n.name AS nname
ORDER BY nname
'''

````

Return:

````
+-----+-----+-------------------------------+
| vid | nid | nname                         |
+-----+-----+-------------------------------+
| 15  | 644 | A.I. Artificial Intelligence  |
| 15  | 497 | Green Mile                    |
| 15  | 377 | Nightmare on Elm Street       |
| 15  | 473 | Pi                            |
| 15  | 129 | Sen To Chihiro No Kamikakushi |
| 15  | 10  | Star Wars                     |
| 15  | 11  | Star Wars: A New Hope         |
+-----+-----+-------------------------------+

````

### Show function information

For information about how to show function information, see [SHOW USER DEFINED FUNCTIONS](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-admin/show/show-user-defined-functions/).

### Drop a function

````
'''
-- Drop the foo() function created in the preceding example
DROP FUNCTION foo

-- Drop if exists
DROP FUNCTION IF EXISTS foo
'''

````