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 owner or editor, namespace owner or editor, or database owner or editor 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 [ OVERWRITE | IF NOT EXISTS ] fn::@name( [ @argument: @type ... ] ) [ -> @value ] {
[ @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.
DEFINE FUNCTION fn::greet($name: string) {
"Hello, " + $name + "!"
};
RETURN fn::greet("Tobie");
To showcase a slightly more complex custom function, this will check if a relation between two nodes exists:
DEFINE FUNCTION fn::relation_exists(
$in: record,
$tb: string,
$out: record
) {
LET $results = SELECT VALUE id FROM type::table($tb) WHERE in = $in AND out = $out;
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);
RETURN fn::last_option(1);
Adding a return value
Optionally, the return value of a function can be specified.
For a function that is infallible, a return value is mostly for the sake of readability.
DEFINE FUNCTION fn::greet($name: string) -> string {
"Hello, " + $name + "!"
};
For a function that is not infallible, specifying a return value can be used to customise error output.
DEFINE FUNCTION fn::combine($one: number, $two: number) -> number {
$one + $two
};
DEFINE FUNCTION fn::combine_any($one: any, $two: any) -> number {
$one + $two
};
fn::combine("one", "two");
fn::combine_any("one", "two");
While both of these return an error, the output of the second function happens only at the point that it attempts to return the combined arguments to the function.
Output
"Expected `number` but found `'one'`"
"Couldn't coerce return value from function `fn::combine_any`: Expected `number` but found `'onetwo'`"
The return value of a function can even be a literal type. The following function returns such a type by either returning an object of a certain structure, or a string. In this case this output is used in case an application prefers to return an error as a simple string instead of throwing an error or returning a NONE value.
DEFINE FUNCTION fn::age_and_name($user_num: int) -> { age: int, name: string } | string {
LET $user = type::record("user", $user_num);
IF $user.exists() {
$user.{ name, age }
} ELSE {
{ "Couldn't find user number " + <string>$user_num + "!" }
}
};
CREATE user:1 SET name = "Billy", age = 15;
fn::age_and_name(1);
fn::age_and_name(2);
Output
{ age: 15, name: 'Billy' }
"Couldn't find user number 2!"
Recursive functions
A function is able to call itself, making it a recursive function. One example of a recursive function is the one below which creates a relation between each and every record passed in.
Consider a situation in which seven person records exist. First, person:1 will need to be related to the rest of the person records, after which there are no more relations to create for it. Following this, the relations for person:2 and all the other records except for person:1 will need to be created, and so on.
This can be done in a recursive function by creating all the relations between the first record and the remaining records, after which the function calls itself by passing in all the records except the first. This continues until the function receives less than two records, in which case it ceases calling itself by doing nothing, thereby ending the recursion.
DEFINE FUNCTION fn::relate_all($records: array<record>) {
IF $records.len() < 2 {
} ELSE {
LET $first = $records[0];
LET $remainder = $records[1..];
FOR $counterpart IN $remainder {
RELATE $first->to->$counterpart;
};
fn::relate_all($remainder);
}
};
CREATE |person:1..8|;
fn::relate_all(SELECT VALUE id FROM person);
SELECT id, ->to->? FROM person;
The last query can be viewed graphically inside Surrealist, leading to an output showing a seven-pointed star.
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.
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 FUNCTION fn::fetchAllPaymentDetails() {
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 FUNCTION fn::fetchAllProducts() {
SELECT * FROM product LIMIT 10
} PERMISSIONS WHERE $auth.admin = true;
Using IF NOT EXISTS clause
The IF NOT EXISTS clause can be used to define a function only if it does not already exist. You should use the IF NOT EXISTS clause when defining a function in SurrealDB if you want to ensure that the function is only created if it does not already exist. If the function already exists, the DEFINE FUNCTION statement will return an error.
It’s particularly useful when you want to safely attempt to define a function without manually checking its existence first.
On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the function definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a function and overwrite an existing one if it already exists, ensuring that the latest version of the function definition is always in use
DEFINE FUNCTION IF NOT EXISTS fn::example() {};
Using OVERWRITE clause
Available since: v2.0.0
The OVERWRITE clause can be used to define a function and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing user definition. If the user already exists, the DEFINE FUNCTION statement will overwrite the existing definition with the new one.
DEFINE FUNCTION OVERWRITE fn::example() {};
Functions as custom middleware
Available since: v3.0.0
A DEFINE FUNCTION statement can be used to define a function for use as custom middleware. For more details on defining a custom function in this manner, see the DEFINE API page.
