SHOW FUNCTIONS

This section explains the SHOW FUNCTIONS command.

Listing the available functions can be done with SHOW FUNCTIONS.

The command SHOW FUNCTIONS only outputs the default output; for a full output use the optional YIELD command. Full output: SHOW FUNCTIONS YIELD *.

This command will produce a table with the following columns:

Table 1. List functions output
Column Description

name

The name of the function. Default Output

category

The function category, for example scalar or string. Default Output

description

The function description. Default Output

signature

The signature of the function.

isBuiltIn

Whether the function is built-in or user-defined.

argumentDescription

List of the arguments for the function, as map of strings with name, type, default, and description.

returnDescription

The return value type.

aggregating

Whether the function is aggregating or not.

rolesExecution

List of roles permitted to execute this function. Is null without the SHOW ROLE privilege.

rolesBoostedExecution

List of roles permitted to use boosted mode when executing this function. Is null without the SHOW ROLE privilege.

1. Syntax

List functions, either all or only built-in or user-defined
SHOW [ALL|BUILT IN|USER DEFINED] FUNCTION[S]
[YIELD { * | field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]
[WHERE expression]
[RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]

When using the RETURN clause, the YIELD clause is mandatory and may not be omitted.

List functions that the current user can execute
SHOW [ALL|BUILT IN|USER DEFINED] FUNCTION[S] EXECUTABLE [BY CURRENT USER]
[YIELD { * | field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]
[WHERE expression]
[RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]

When using the RETURN clause, the YIELD clause is mandatory and may not be omitted.

List functions that the specified user can execute
SHOW [ALL|BUILT IN|USER DEFINED] FUNCTION[S] EXECUTABLE BY username
[YIELD { * | field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]
[WHERE expression]
[RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]

Required privilege SHOW USER. This command cannot be used for LDAP users.

When using the RETURN clause, the YIELD clause is mandatory and may not be omitted.

2. Listing all functions

To list all available functions with the default output columns, the SHOW FUNCTIONS command can be used. If all columns are required, use SHOW FUNCTIONS YIELD *.

Query
SHOW FUNCTIONS
Table 2. Result
name category description

"abs"

"Numeric"

"Returns the absolute value of an integer."

"abs"

"Numeric"

"Returns the absolute value of a floating point number."

"acos"

"Trigonometric"

"Returns the arccosine of a number in radians."

"all"

"Predicate"

"Returns true if the predicate holds for all elements in the given list."

"any"

"Predicate"

"Returns true if the predicate holds for at least one element in the given list."

"asin"

"Trigonometric"

"Returns the arcsine of a number in radians."

"atan"

"Trigonometric"

"Returns the arctangent of a number in radians."

"atan2"

"Trigonometric"

"Returns the arctangent2 of a set of coordinates in radians."

"avg"

"Aggregating"

"Returns the average of a set of integer values."

"avg"

"Aggregating"

"Returns the average of a set of floating point values."

"avg"

"Aggregating"

"Returns the average of a set of duration values."

"ceil"

"Numeric"

"Returns the smallest floating point number that is greater than or equal to a number and equal to a mathematical integer."

"coalesce"

"Scalar"

"Returns the first non-null value in a list of expressions."

"collect"

"Aggregating"

"Returns a list containing the values returned by an expression."

"cos"

"Trigonometric"

"Returns the cosine of a number."

"cot"

"Trigonometric"

"Returns the cotangent of a number."

"count"

"Aggregating"

"Returns the number of values or rows."

"date"

"Temporal"

"Create a Date instant."

"date.realtime"

"Temporal"

"Get the current Date instant using the realtime clock."

"date.statement"

"Temporal"

"Get the current Date instant using the statement clock."

Rows: 20

3. Listing functions with filtering on output columns

The listed functions can be filtered in multiple ways. One way is through the type keywords, BUILT IN and USER DEFINED. A more flexible way is to use the WHERE clause. For example, getting the name of all built-in functions starting with the letter 'a':

Query
SHOW BUILT IN FUNCTIONS YIELD name, isBuiltIn
WHERE name STARTS WITH 'a'
Table 3. Result
name isBuiltIn

"abs"

true

"abs"

true

"acos"

true

"all"

true

"any"

true

"asin"

true

"atan"

true

"atan2"

true

"avg"

true

"avg"

true

"avg"

true

Rows: 11

4. Listing functions with other filtering

The listed functions can also be filtered on whether a user can execute them. This filtering is only available through the EXECUTABLE clause and not through the WHERE clause. This is due to using the user’s privileges instead of filtering on the available output columns.

There are two options, how to use the EXECUTABLE clause. The first option, is to filter for the current user:

Query
SHOW FUNCTIONS EXECUTABLE BY CURRENT USER YIELD *
Table 4. Result
name category description rolesExecution rolesBoostedExecution ...

"abs"

"Numeric"

"Returns the absolute value of an integer."

<null>

<null>

"abs"

"Numeric"

"Returns the absolute value of a floating point number."

<null>

<null>

"acos"

"Trigonometric"

"Returns the arccosine of a number in radians."

<null>

<null>

"all"

"Predicate"

"Returns true if the predicate holds for all elements in the given list."

<null>

<null>

"any"

"Predicate"

"Returns true if the predicate holds for at least one element in the given list."

<null>

<null>

"asin"

"Trigonometric"

"Returns the arcsine of a number in radians."

<null>

<null>

"atan"

"Trigonometric"

"Returns the arctangent of a number in radians."

<null>

<null>

"atan2"

"Trigonometric"

"Returns the arctangent2 of a set of coordinates in radians."

<null>

<null>

"avg"

"Aggregating"

"Returns the average of a set of integer values."

<null>

<null>

"avg"

"Aggregating"

"Returns the average of a set of floating point values."

<null>

<null>

Rows: 10

Notice that the two roles columns are empty due to missing the SHOW ROLE privilege.

The second option, is to filter for a specific user:

Query
SHOW FUNCTIONS EXECUTABLE BY jake
Table 5. Result
name category description

"abs"

"Numeric"

"Returns the absolute value of an integer."

"abs"

"Numeric"

"Returns the absolute value of a floating point number."

"acos"

"Trigonometric"

"Returns the arccosine of a number in radians."

"all"

"Predicate"

"Returns true if the predicate holds for all elements in the given list."

"any"

"Predicate"

"Returns true if the predicate holds for at least one element in the given list."

"asin"

"Trigonometric"

"Returns the arcsine of a number in radians."

"atan"

"Trigonometric"

"Returns the arctangent of a number in radians."

"atan2"

"Trigonometric"

"Returns the arctangent2 of a set of coordinates in radians."

"avg"

"Aggregating"

"Returns the average of a set of integer values."

"avg"

"Aggregating"

"Returns the average of a set of floating point values."

Rows: 10