Functions are ways to transform one set of data into another. They are defined in modules and are part of the database schema.

For example, consider the function len() used to transform a set of str into a set of int64:

Copy
db> 
SELECT len({'hello', 'world'});
{5, 5}

This behavior is known as an element-wise operation. Many built-in and user-defined functions operate on elements. In case of multiple arguments, a cross-product of all the input sets is computed to determine all the input combinations that the function needs to be applied to. After that, the function is applied to each element of the cross-product. The results of this function application form the output set. This implies that if any of the input sets are empty, the result of applying an element-wise function is also empty.

Compare that with the aggregate function count() that transforms a set of str into a single int64 value, representing the set cardinality.

For example, count() maps a set to an integer, specifically it returns the number of elements in a set:

Copy
db> 
SELECT count({'hello', 'world'});
{2}

Here’s an example of array_agg() mapping a set to an array and ordering it in the process:

Copy
db> 
... 
WITH names := {'Alice', 'Dana', 'Billie', 'Cameron'}
SELECT array_agg(names ORDER BY names);
{['Alice', 'Billie', 'Cameron', 'Dana']}

It is also possible 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!'}

In order to make sure that the function is called when the argument is an empty set {} we make the argument optional. We also provide a default value of {} if the argument is omitted entirely. Here are some results this function produces:

Copy
test> 
SELECT exclamation({'Hello', 'World'});
{'Hello!', 'World!'}
Copy
test> 
SELECT exclamation(<str>{});
{'!!!'}
Copy
test> 
SELECT exclamation();
{'!!!'}

Function SDL, DDL, and introspection.

Light
Dark
System