Search...
ctrl/
Light
Dark
System
Sign in

Functions​

This page documents how to define custom functions, however Gel provides a large library of built-in functions and operators. These are documented in Standard Library.

User-defined Functions​

Gel allows you to define custom functions. For example, consider a function that adds an exclamation mark '!' at the end of the string:

Copy
function exclamation(word: str) -> str
  using (word ++ '!');

This function accepts a str as an argument and produces a str as output as well.

Copy
test> 
select exclamation({'Hello', 'World'});
{'Hello!', 'World!'}

Sets as arguments​

Calling a user-defined function on a set will always apply it as *element-wise*.

Copy
function magnitude(x: float64) -> float64
  using (
    math::sqrt(sum(x * x))
  );
Copy
db> 
select magnitude({3, 4});
{3, 4}

In order to pass in multiple arguments at once, arguments should be packed into arrays:

Copy
function magnitude(xs: array<float64>) -> float64
  using (
    with x := array_unpack(xs)
    select math::sqrt(sum(x * x))
  );
Copy
db> 
select magnitude([3, 4]);
{5}

Multiple packed arrays can be passed into such a function, which will then be applied element-wise.

Copy
db> 
select magnitude({[3, 4], [5, 12]});
{5, 13}

Modifying FunctionsNew​

User-defined functions can contain DML (i.e., insert, update, delete) to make changes to existing data. These functions have a modifying volatility.

Copy
function add_user(name: str) -> User
  using (
    insert User {
      name := name,
      joined_at := std::datetime_current(),
    }
  );
Copy
db> 
select add_user('Jan') {name, joined_at};
{default::User {name: 'Jan', joined_at: <datetime>'2024-12-11T11:49:47Z'}}

Unlike other functions, the arguments of modifying functions must have a cardinality of One.

Copy
db> 
select add_user({'Feb','Mar'});
gel error: QueryError: possibly more than one element passed into
modifying function
Copy
db> 
select add_user(<str>{});
gel error: QueryError: possibly an empty set passed as non-optional
argument into modifying function

Optional arguments can still accept empty sets. For example, if add_user was defined as:

Copy
function add_user(name: str, joined_at: optional datetime) -> User
  using (
    insert User {
      name := name,
      joined_at := joined_at ?? std::datetime_current(),
    }
  );

then the following queries are valid:

Copy
db> 
select add_user('Apr', <datetime>{}) {name, joined_at};
{default::User {name: 'Apr', joined_at: <datetime>'2024-12-11T11:50:51Z'}}
Copy
db> 
select add_user('May', <datetime>'2024-12-11T12:00:00-07:00') {name, joined_at};
{default::User {name: 'May', joined_at: <datetime>'2024-12-11T12:00:00Z'}}

In order to insert or update a multi parameter, the desired arguments should be aggregated into an array as described above:

Copy
function add_user(name: str, nicknames: array<str>) -> User
  using (
    insert User {
      name := name,
      nicknames := array_unpack(nicknames),
    }
  );

Declaring functions​

This section describes the syntax to declare a function in your schema.

Syntax​

function name ([ argspec ] [, ... ]) -> returnspec
using ( edgeql );

function name ([ argspec ] [, ... ]) -> returnspec
using language functionbody ;

function name ([ argspec ] [, ... ]) -> returnspec
"{"
    [ annotation-declarations ]
    [ volatility := {'Immutable' | 'Stable' | 'Volatile' | 'Modifying'} ]
    [ using ( expr ) ; ]
    [ using language functionbody ; ]
    [ ... ]
"}" ;

where argspec is:

[ argkind ] argname: [ typequal ] argtype [ = default ]

argkind is:

[ { variadic | named only } ]

typequal is:

[ { set of | optional } ]

and returnspec is:

[ typequal ] rettype

Description​

This declaration defines a new function with the following options:

name

The name (optionally module-qualified) of the function to create.

argkind

The kind of an argument: variadic or named only.

If not specified, the argument is called positional.

The variadic modifier indicates that the function takes an arbitrary number of arguments of the specified type. The passed arguments will be passed as an array of the argument type. Positional arguments cannot follow a variadic argument. variadic parameters cannot have a default value.

The named only modifier indicates that the argument can only be passed using that specific name. Positional arguments cannot follow a named only argument.

argname

The name of an argument. If named only modifier is used this argument must be passed using this name only.

typequal

The type qualifier: set of or optional.

The set of qualifier indicates that the function is taking the argument as a whole set, as opposed to being called on the input product element-by-element.

User defined functions can not use set of arguments.

The optional qualifier indicates that the function will be called if the argument is an empty set. The default behavior is to return an empty set if the argument is not marked as optional.

argtype

The data type of the function's arguments (optionally module-qualified).

default

An expression to be used as default value if the parameter is not specified. The expression has to be of a type compatible with the type of the argument.

rettype

The return data type (optionally module-qualified).

The set of modifier indicates that the function will return a non-singleton set.

The optional qualifier indicates that the function may return an empty set.

The valid SDL sub-declarations are listed below:

volatility := {'Immutable' | 'Stable' | 'Volatile' | 'Modifying'}

Function volatility determines how aggressively the compiler can optimize its invocations.

If not explicitly specified the function volatility is inferred from the function body.

  • An Immutable function cannot modify the database and is guaranteed to return the same results given the same arguments in all statements.

  • A Stable function cannot modify the database and is guaranteed to return the same results given the same arguments within a single statement.

  • A Volatile function cannot modify the database and can return different results on successive calls with the same arguments.

  • A Modifying function can modify the database and can return different results on successive calls with the same arguments.

using ( expr )

Specifies the body of the function. expr is an arbitrary EdgeQL expression.

using language functionbody

A verbose version of the using clause that allows specifying the language of the function body.

  • language is the name of the language that the function is implemented in. Currently can only be edgeql.

  • functionbody is a string constant defining the function. It is often helpful to use dollar quoting to write the function definition string.

annotation-declarations

Set function annotation to a given value.

The function name must be distinct from that of any existing function with the same argument types in the same module. Functions of different argument types can share a name, in which case the functions are called overloaded functions.

DDL commands​

This section describes the low-level DDL commands for creating, altering, and dropping functions. You typically don't need to use these commands directly, but knowing about them is useful for reviewing migrations.

Create function​

Define a new function.

[ with with-item [, ...] ]
create function name ([ argspec ] [, ... ]) -> returnspec
using ( expr );

[ with with-item [, ...] ]
create function name ([ argspec ] [, ... ]) -> returnspec
using language functionbody ;

[ with with-item [, ...] ]
create function name ([ argspec ] [, ... ]) -> returnspec
"{" subcommand [, ...] "}" ;

where argspec is:

  [ argkind ] argname: [ typequal ] argtype [ = default ]

argkind is:

  [ { variadic | named only } ]

typequal is:

  [ { set of | optional } ]

and returnspec is:

  [ typequal ] rettype

and subcommand is one of

  set volatility := {'Immutable' | 'Stable' | 'Volatile' | 'Modifying'} ;
  create annotation annotation-name := value ;
  using ( expr ) ;
  using language functionbody ;

Description​

The command create function defines a new function. If name is qualified with a module name, then the function is created in that module, otherwise it is created in the current module.

The function name must be distinct from that of any existing function with the same argument types in the same module. Functions of different argument types can share a name, in which case the functions are called overloaded functions.

Parameters​

Most sub-commands and options of this command are identical to the SDL function declaration, with some additional features listed below:

set volatility := {'Immutable' | 'Stable' | 'Volatile' | 'Modifying'}

Function volatility determines how aggressively the compiler can optimize its invocations. Other than a slight syntactical difference this is the same as the corresponding SDL declaration.

create annotation annotation-name := value

Set the function's annotation-name to value.

See create annotation for details.

Examples​

Define a function returning the sum of its arguments:

Copy
create function mysum(a: int64, b: int64) -> int64
using (
    select a + b
);

The same, but using a variadic argument and an explicit language:

Copy
create function mysum(variadic argv: int64) -> int64
using edgeql $$
    select sum(array_unpack(argv))
$$;

Define a function using the block syntax:

Copy
create function mysum(a: int64, b: int64) -> int64 {
    using (
        select a + b
    );
    create annotation title := "My sum function.";
};

Alter function​

Change the definition of a function.

[ with with-item [, ...] ]
alter function name ([ argspec ] [, ... ]) "{"
    subcommand [, ...]
"}"

where argspec is:

[ argkind ] argname: [ typequal ] argtype [ = default ]

and subcommand is one of

  set volatility := {'Immutable' | 'Stable' | 'Volatile' | 'Modifying'} ;
  reset volatility ;
  rename to newname ;
  create annotation annotation-name := value ;
  alter annotation annotation-name := value ;
  drop annotation annotation-name ;
  using ( expr ) ;
  using language functionbody ;

Description​

The command alter function changes the definition of a function. The command allows changing annotations, the volatility level, and other attributes.

Subcommands​

The following subcommands are allowed in the alter function block in addition to the commands common to the create function:

reset volatility

Remove explicitly specified volatility in favor of the volatility inferred from the function body.

rename to newname

Change the name of the function to newname.

alter annotation annotation-name;

Alter function annotation-name. See alter annotation for details.

drop annotation annotation-name;

Remove function annotation-name. See drop annotation for details.

reset errmessage;

Remove the error message from this abstract constraint. The error message specified in the base abstract constraint will be used instead.

Example​

Copy
create function mysum(a: int64, b: int64) -> int64 {
    using (
        select a + b
    );
    create annotation title := "My sum function.";
};

alter function mysum(a: int64, b: int64) {
    set volatility := 'Immutable';
    drop annotation title;
};

alter function mysum(a: int64, b: int64) {
    using (
        select (a + b) * 100
    )
};

Drop function​

Remove a function.

[ with with-item [, ...] ]
drop function name ([ argspec ] [, ... ]);

where argspec is:

[ argkind ] argname: [ typequal ] argtype [ = default ]

Description​

The command drop function removes the definition of an existing function. The argument types to the function must be specified, since there can be different functions with the same name.

Parameters​

name

The name (optionally module-qualified) of an existing function.

argname

The name of an argument used in the function definition.

argmode

The mode of an argument: set of or optional or variadic.

argtype

The data type(s) of the function's arguments (optionally module-qualified), if any.

Example​

Remove the mysum function:

Copy
drop function mysum(a: int64, b: int64);