Lambda Functions

Lambda functions enable the use of more complex and flexible expressions in queries. SereneDB supports several scalar functions that operate on LISTs and accept lambda functions as parameters in the form lambda ⟨parameter1⟩, ⟨parameter2⟩, ... : ⟨expression⟩. If the lambda function has only one parameter, then the parentheses can be omitted. The parameters can have any names. For example, the following are all valid lambda functions:

lambda param : param > 1
lambda s : contains(concat(s, 'DB'), 'DB')
lambda acc, x : acc + x

Scalar Functions That Accept Lambda Functions

Function	Description
`apply(list, lambda(x))`	Alias for `list_transform`.
`array_apply(list, lambda(x))`	Alias for `list_transform`.
`array_filter(list, lambda(x))`	Alias for `list_filter`.
`array_reduce(list, lambda(x, y)[, initial_value])`	Alias for `list_reduce`.
`array_transform(list, lambda(x))`	Alias for `list_transform`.
`filter(list, lambda(x))`	Alias for `list_filter`.
`list_apply(list, lambda(x))`	Alias for `list_transform`.
`list_filter(list, lambda(x))`	Constructs a list from those elements of the input `list` for which the `lambda` function returns `true`. SereneDB must be able to cast the `lambda` function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See `list_filter` examples.
`list_reduce(list, lambda(x, y)[, initial_value])`	Reduces all elements of the input `list` into a single scalar value by executing the `lambda` function on a running result and the next list element. The `lambda` function has an optional `initial_value` argument. See `list_reduce` examples.
`list_transform(list, lambda(x))`	Returns a list that is the result of applying the `lambda` function to each element of the input `list`. The return type is defined by the return type of the `lambda` function. See `list_transform` examples.
`reduce(list, lambda(x, y)[, initial_value])`	Alias for `list_reduce`.

`list_filter(list, lambda(x))`

Constructs a list from those elements of the input list for which the lambda function returns true. SereneDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's. See list_filter examples. Aliases: array_filter, filter.

SQL

SELECT list_filter([3, 4, 5], lambda x : x > 4) AS list_filter;

 list_filter
-------------
 {5}

`list_reduce(list, lambda(x, y)[, initial_value])`

Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument. See list_reduce examples. Aliases: array_reduce, reduce.

SQL

SELECT list_reduce([1, 2, 3], lambda x, y : x + y) AS list_reduce;

 list_reduce
-------------
           6

`list_transform(list, lambda(x))`

Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function. See list_transform examples. Aliases: apply, array_apply, array_transform, list_apply.

SQL

SELECT list_transform([1, 2, 3], lambda x : x + 1) AS list_transform;

 list_transform
----------------
 {2,3,4}

Nesting Lambda Functions

All scalar functions can be arbitrarily nested. For example, nested lambda functions to get all squares of even list elements:

SQL

SELECT list_transform(        list_filter([0, 1, 2, 3, 4, 5], lambda x: x % 2 = 0),        lambda y: y * y    );

 list_transform
----------------
 {0,4,16}

Nested lambda function to add each element of the first list to the sum of the second list:

SQL

SELECT list_transform(        [1, 2, 3],        lambda x :            list_reduce([4, 5, 6], lambda a, b: a + b) + x    );

 list_transform
----------------
 {16,17,18}

Scoping

Lambda functions conform to scoping rules in the following order:

inner lambda parameters
outer lambda parameters
column names
macro parameters

SQL

CREATE TABLE tbl (x INTEGER);
INSERT INTO tbl VALUES (10);
SELECT list_apply(            [1, 2],            lambda x: list_apply([4], lambda x: x + tbl.x)[1] + x    )FROM tbl;

 list_apply
------------
 {15,16}

Indexes as Parameters

All lambda functions accept an optional extra parameter that represents the index of the current element. This is always the last parameter of the lambda function (e.g., i in (x, i)), and is 1-based (i.e., the first element has index 1).

Get all elements that are larger than their index:

SQL

SELECT list_filter([1, 3, 1, 5], lambda x, i: x > i);

 list_filter
-------------
 {3,5}

Examples

`list_transform` Examples

Incrementing each list element by one:

SQL

SELECT list_transform([1, 2, NULL, 3], lambda x: x + 1);

 list_transform
----------------
 {2,3,NULL,4}

Transforming strings:

SQL

SELECT list_transform(['Serene', 'Calm', 'Tranquil'], lambda s: concat(s, 'DB'));

 list_transform
------------------------------
 {SereneDB,CalmDB,TranquilDB}

Combining lambda functions with other functions:

SQL

SELECT list_transform([5, NULL, 6], lambda x: coalesce(x, 0) + 1);

 list_transform
----------------
 {6,1,7}

`list_filter` Examples

Filter out negative values:

SQL

SELECT list_filter([5, -6, NULL, 7], lambda x: x > 0);

 list_filter
-------------
 {5,7}

Divisible by 2 and 5:

SQL

SELECT list_filter(        list_filter([2, 4, 3, 1, 20, 10, 3, 30], lambda x: x % 2 = 0),        lambda y: y % 5 = 0    );

 list_filter
-------------
 {20,10,30}

In combination with range(...) to construct lists:

SQL

SELECT list_filter([1, 2, 3, 4], lambda x: x > range) FROM range(4);

 list_filter
-------------
 {1,2,3,4}
 {2,3,4}
 {3,4}
 {4}

`list_reduce` Examples

Sum of all list elements:

SQL

SELECT list_reduce([1, 2, 3, 4], lambda acc, x: acc + x);

 list_reduce
-------------
          10

Only add up list elements if they are greater than 2:

SQL

SELECT list_reduce(        list_filter([1, 2, 3, 4], lambda x: x > 2),        lambda acc, x: acc + x    );

 list_reduce
-------------
           7

Concat all list elements:

SQL

SELECT list_reduce(['SereneDB', 'is', 'awesome'], lambda acc, x: concat(acc, ' ', x));

 list_reduce
---------------------
 SereneDB is awesome

Concatenate elements with the index without an initial value:

SQL

SELECT list_reduce(        ['a', 'b', 'c', 'd'],        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y    );

 list_reduce
---------------------------
 a - 2 - b - 3 - c - 4 - d

Concatenate elements with the index with an initial value:

SQL

SELECT list_reduce(        ['a', 'b', 'c', 'd'],        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y, 'INITIAL'    );

 list_reduce
-----------------------------------------
 INITIAL - 1 - a - 2 - b - 3 - c - 4 - d

Limitations

Subqueries in lambda expressions are currently not supported. For example:

SQL

SELECT list_apply([1, 2, 3], lambda x: (SELECT 42) + x);

error db error: ERROR: subqueries in lambda expressions are not supported

Scalar Functions That Accept Lambda Functions​

list_filter(list, lambda(x))​

list_reduce(list, lambda(x, y)[, initial_value])​

list_transform(list, lambda(x))​

Nesting Lambda Functions​

Scoping​

Indexes as Parameters​

Examples​

list_transform Examples​

list_filter Examples​

list_reduce Examples​

Limitations​