Version: 2.x(alpha)

`DEFINE FUNCTION` statement

The DEFINE FUNCTION statement allows you to define custom functions that can be reused throughout a database. When using the DEFINE FUNCTION statement, you can define a function that takes one or more arguments and returns a value. You can then call this function in other SurrealQL statements.

Functions can be used to encapsulate logic that you want to reuse in multiple queries. They can also be used to simplify complex queries by breaking them down into smaller, more manageable pieces. The are particularly useful when you have a complex query that you need to run multiple times with different arguments.

Requirements

You must be authenticated as a root or namespace user before you can use the DEFINE FUNCTION statement.
You must select your namespace and database before you can use the DEFINE FUNCTION statement.

Statement syntax

SurrealQL Syntax
DEFINE FUNCTION [ IF NOT EXISTS ] fn::@name( [ @argument: @type ... ] ) {
	[ @query ... ]
	[ RETURN @returned ]
} [ COMMENT @string ] [ PERMISSIONS [ NONE | FULL | WHERE @condition]]

Example usage

Below shows how you can define a custom function using the DEFINE FUNCTION statement, and how to call it.

-- It is necessary to prefix the name of your function with "fn::"
-- This indicates that it's a custom function
DEFINE FUNCTION fn::greet($name: string) {
	RETURN "Hello, " + $name + "!";
}

-- Returns: "Hello, Tobie!"
RETURN fn::greet("Tobie");

To showcase a slightly more complex custom function, this will check if a relation between two nodes exists:

-- Define a function that checks if a relation exists between two nodes
DEFINE FUNCTION fn::relation_exists(
	$in: record,
	$tb: string,
	$out: record
) {
	-- Check if a relation exists between the two nodes. 
	LET $results = SELECT VALUE id FROM type::table($tb) WHERE in = $in AND out = $out;
	-- Return true if a relation exists, false otherwise
    RETURN array::len($results) > 0;
};

Optional arguments

If one or more ending arguments have the option<T> type, they can be omitted when you run the invoke the function.

DEFINE FUNCTION fn::last_option($required: number, $optional: option<number>) {
	RETURN {
		required_present: type::is::number($required),
		optional_present: type::is::number($optional),
	}
};

RETURN fn::last_option(1, 2);
{
	required_present: true,
	optional_present: true,
};

RETURN fn::last_option(1);
{
	required_present: true,
	optional_present: false,
};

Permissions

You can set the permissions for a custom function using the PERMISSIONS clause. The PERMISSIONS clause is mostly used to restrict who can access a function and what data they can access. It can be set to NONE, FULL, or WHERE @condition.

FULL: When Full permissions are granted record users have access to the function. This is the default permission when not specified.
NONE: When this permission is granted, record users have no access to the defined function.
WHERE @condition: Permissions are granted to the function based on the specified condition.

Note: The examples below use the Surreal Deal Store dataset.

Using the `FULL` permission

The FULL permission grants all users access to the function. The following example defines a function that fetches all products from the product table and grants the function full permissions to access the data to all users.

Using the `NONE` permission

The NONE permission denies all record users access to the function. The following example defines a function that fetches all products from the product table

-- Define a function that fetches all expiration years from the payment_details table and denies access to all none-admin users
DEFINE FUNCTION fn::fetchAllPaymentDetails() {
	RETURN (SELECT stored_cards.expiry_year FROM payment_details LIMIT 5);
} PERMISSIONS NONE;

RETURN fn::fetchAllPaymentDetails();

Using the `WHERE` clause

The WHERE clause allows you to specify a condition that determines the permissions granted to the function. The condition must evaluate to a boolean value. If the condition evaluates to true, the function is granted permissions. If the condition evaluates to false, the function is not granted permissions.

-- Define a function that fetches all products with the condition that only admin users can access it
DEFINE FUNCTION fn::fetchAllProducts() {
	RETURN (SELECT * FROM product LIMIT 10);
} PERMISSIONS WHERE $auth.admin = true;

Using `IF NOT EXISTS` clause Since 1.3.0

The IF NOT EXISTS clause can be used to define a function only if it does not already exist. If the function already exists, the DEFINE FUNCTION statement will return an error.

-- Create a FUNCTION if it does not already exist
DEFINE FUNCTION IF NOT EXISTS fn::example() {};

DEFINE FUNCTION statement

Requirements​

Statement syntax​

Example usage​

Optional arguments​

Permissions​

Using the FULL permission​

Using the NONE permission​

Using the WHERE clause​

Using IF NOT EXISTS clause Since 1.3.0​