Overview

Function Syntax

Function Chaining via the Dot Operator

SereneDB supports the dot syntax for function chaining. This allows the function call fn(arg1, arg2, arg3, ...) to be rewritten as arg1.fn(arg2, arg3, ...). For example, take the following use of the replace function:

Query

SELECT replace(goose_name, 'goose', 'duck') AS duck_nameFROM unnest(['African goose', 'Faroese goose', 'Hungarian goose', 'Pomeranian goose']) breed(goose_name);

Result

 duck_name----------------- African duck Faroese duck Hungarian duck Pomeranian duck

This can be rewritten as follows:

Query

SELECT goose_name.replace('goose', 'duck') AS duck_nameFROM unnest(['African goose', 'Faroese goose', 'Hungarian goose', 'Pomeranian goose']) breed(goose_name);

Result

 duck_name----------------- African duck Faroese duck Hungarian duck Pomeranian duck

Using with Literals and Arrays

Function chaining also works on literals and on the result of array access. Wrapping the argument in parentheses is optional, but makes the intent explicit:

Query

SELECT ('hello world').replace(' ', '_');

Result

 replace------------- hello_world

Query

SELECT (2).sqrt();

Result

 sqrt-------------------- 1.4142135623730951

Query

SELECT (m[1]).map_entries()FROM (VALUES ([MAP {'hello': 42}, MAP {'world': 42}])) t(m);

Result

 map_entries---------------- {"(hello,42)"}

Limitations

Function chaining via the dot operator is limited to scalar functions and is not supported for table functions. For example, the following call returns a Parser Error:

Query

SELECT * FROM ('my_file.parquet').read_parquet();

Result

db error: ERROR: syntax error at or near "."

Additionally, the functions coalesce and ifnull cannot be used with function chaining for the time being:

Query

SELECT (2).coalesce(0);
SELECT (2).ifnull(0);

Result

db error: ERROR: Scalar Function with name coalesce does not exist!Did you mean "(varies)"?
db error: ERROR: Scalar Function with name ifnull does not exist!Did you mean "fill"?

Query Functions

The duckdb_functions() table function shows the list of functions currently built into the system.

Query

SELECT DISTINCT ON(function_name)    function_name,    function_type,    return_type,    parameter_typesFROM sdb_functions()WHERE function_type = 'scalar'  AND function_name LIKE 'b%'ORDER BY function_name;

Result

 function_name             | function_type | return_type | parameter_types---------------------------+---------------+-------------+------------------------------- bar                       | scalar        | VARCHAR     | {DOUBLE,DOUBLE,DOUBLE,DOUBLE} base64                    | scalar        | VARCHAR     | {BLOB} bin                       | scalar        | VARCHAR     | {VARCHAR} bit_count                 | scalar        | TINYINT     | {TINYINT} bit_length                | scalar        | BIGINT      | {VARCHAR} bit_position              | scalar        | INTEGER     | {BIT,BIT} bitstring                 | scalar        | BIT         | {VARCHAR,INTEGER} bitstring_byte_comparable | scalar        | BLOB        | {BIT} bm25                      | scalar        | FLOAT       | {BIGINT} broadcast                 | scalar        | INET        | {INET}

In addition to the columns shown above, duckdb_functions() exposes the parameters and description columns, which provide the parameter names and a human-readable description (where available) for each function.