Skip to main content

Functions

Function Syntax​

Function Chaining via the Dot Operator​

Goose 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:

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

This can be rewritten as follows:

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

Using with Literals and Arrays​

To apply function chaining to literals and following array access operations, you must surround the argument with parentheses, e.g.:

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

In the absence of these parentheses, Goose will return a Parser Error for the function call:

Parser Error:
syntax error at or near "("

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:

SELECT * FROM ('my_file.parquet').read_parquet(); -- does not work

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

SELECT (2).coalesce(0); -- does not work
SELECT (2).ifnull(0); -- does not work

Query Functions​

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

SELECT DISTINCT ON(function_name)
    function_name,
    function_type,
    return_type,
    parameters,
    parameter_types,
    description
FROM goose_functions()
WHERE function_type = 'scalar'
  AND function_name LIKE 'b%'
ORDER BY function_name;
function_namefunction_typereturn_typeparametersparameter_typesdescription
barscalarVARCHAR[x, min, max, width][DOUBLE, DOUBLE, DOUBLE, DOUBLE]Draws a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80
base64scalarVARCHAR[blob][BLOB]Convert a blob to a base64 encoded string
binscalarVARCHAR[value][VARCHAR]Converts the value to binary representation
bit_countscalarTINYINT[x][TINYINT]Returns the number of bits that are set
bit_lengthscalarBIGINT[col0][VARCHAR]Number of bits in a string
bit_positionscalarINTEGER[substring, bitstring][BIT, BIT]Returns first starting index of the specified substring within bits, or zero if it is not present. The first (leftmost) bit is indexed 1
bitstringscalarBIT[bitstring, length][VARCHAR, INTEGER]Pads the bitstring until the specified length

Currently, the description and parameter names of functions are not available in the goose_functions() function.