Skip to main content
SereneDBSereneDB
897Contact Us

Overview

Function Syntax​

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:

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

This can be rewritten as follows:

SQL
SELECT goose_name.replace('goose', 'duck') AS duck_nameFROM unnest(['African goose', 'Faroese goose', 'Hungarian goose', 'Pomeranian goose']) breed(goose_name);
 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:

SQL
SELECT ('hello world').replace(' ', '_');
 replace
-------------
 hello_world
SQL
SELECT (2).sqrt();
 sqrt
--------------------
 1.4142135623730951
SQL
SELECT (m[1]).map_entries()FROM (VALUES ([MAP {'hello': 42}, MAP {'world': 42}])) t(m);
 map_entries
----------------
 {"(hello,42)"}

coalesce and ifnull chain the same way:

SQL
SELECT (NULL::INTEGER).coalesce(0);
SELECT (2).ifnull(0);
 coalesce
----------
        0

 coalesce
----------
        2

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:

SQL
SELECT * FROM ('my_file.parquet').read_parquet();
db error: ERROR: syntax error at or near "."

Query Functions​

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

SQL
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;
 function_name             | function_type | return_type | parameter_types
---------------------------+---------------+-------------+-------------------------------
 bar                       | scalar        | VARCHAR     | {DOUBLE,DOUBLE,DOUBLE,DOUBLE}
 base64                    | scalar        | VARCHAR     | {BLOB}
 bin                       | scalar        | VARCHAR     | {VARCHAR}
 binom                     | scalar        | HUGEINT     | {INTEGER,INTEGER}
 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, sdb_functions() exposes the parameters and description columns, which provide the parameter names and a human-readable description (where available) for each function.

Edit this page ↗