SHOW FUNCTIONS

edit

This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

Synopsis.

SHOW FUNCTIONS [ LIKE pattern? ]? 

SQL match pattern

DescriptionList all the SQL functions and their type. The LIKE clause can be used to restrict the list of names to the given pattern.

SHOW FUNCTIONS;

      name      |     type
----------------+---------------
AVG             |AGGREGATE
COUNT           |AGGREGATE
MAX             |AGGREGATE
MIN             |AGGREGATE
SUM             |AGGREGATE
KURTOSIS        |AGGREGATE
PERCENTILE      |AGGREGATE
PERCENTILE_RANK |AGGREGATE
SKEWNESS        |AGGREGATE
STDDEV_POP      |AGGREGATE
SUM_OF_SQUARES  |AGGREGATE
VAR_POP         |AGGREGATE
DAY             |SCALAR
DAYNAME         |SCALAR
DAYOFMONTH      |SCALAR
DAYOFWEEK       |SCALAR
DAYOFYEAR       |SCALAR
DAY_NAME        |SCALAR
DAY_OF_MONTH    |SCALAR
DAY_OF_WEEK     |SCALAR
DAY_OF_YEAR     |SCALAR
DOM             |SCALAR
DOW             |SCALAR
DOY             |SCALAR
HOUR            |SCALAR
HOUR_OF_DAY     |SCALAR
MINUTE          |SCALAR
MINUTE_OF_DAY   |SCALAR
MINUTE_OF_HOUR  |SCALAR
MONTH           |SCALAR
MONTHNAME       |SCALAR
MONTH_NAME      |SCALAR
MONTH_OF_YEAR   |SCALAR
QUARTER         |SCALAR
SECOND          |SCALAR
SECOND_OF_MINUTE|SCALAR
WEEK            |SCALAR
WEEK_OF_YEAR    |SCALAR
YEAR            |SCALAR
ABS             |SCALAR
ACOS            |SCALAR
ASIN            |SCALAR
ATAN            |SCALAR
ATAN2           |SCALAR
CBRT            |SCALAR
CEIL            |SCALAR
CEILING         |SCALAR
COS             |SCALAR
COSH            |SCALAR
COT             |SCALAR
DEGREES         |SCALAR
E               |SCALAR
EXP             |SCALAR
EXPM1           |SCALAR
FLOOR           |SCALAR
LOG             |SCALAR
LOG10           |SCALAR
MOD             |SCALAR
PI              |SCALAR
POWER           |SCALAR
RADIANS         |SCALAR
RAND            |SCALAR
RANDOM          |SCALAR
ROUND           |SCALAR
SIGN            |SCALAR
SIGNUM          |SCALAR
SIN             |SCALAR
SINH            |SCALAR
SQRT            |SCALAR
TAN             |SCALAR
TRUNCATE        |SCALAR
ASCII           |SCALAR
BIT_LENGTH      |SCALAR
CHAR            |SCALAR
CHARACTER_LENGTH|SCALAR
CHAR_LENGTH     |SCALAR
CONCAT          |SCALAR
INSERT          |SCALAR
LCASE           |SCALAR
LEFT            |SCALAR
LENGTH          |SCALAR
LOCATE          |SCALAR
LTRIM           |SCALAR
OCTET_LENGTH    |SCALAR
POSITION        |SCALAR
REPEAT          |SCALAR
REPLACE         |SCALAR
RIGHT           |SCALAR
RTRIM           |SCALAR
SPACE           |SCALAR
SUBSTRING       |SCALAR
UCASE           |SCALAR
CAST            |SCALAR
CONVERT         |SCALAR
SCORE           |SCORE

The list of functions returned can be customized based on the pattern.

It can be an exact match:

SHOW FUNCTIONS LIKE 'ABS';

     name      |     type
---------------+---------------
ABS            |SCALAR

A wildcard for exactly one character:

SHOW FUNCTIONS LIKE 'A__';

     name      |     type
---------------+---------------
AVG            |AGGREGATE
ABS            |SCALAR

A wildcard matching zero or more characters:

SHOW FUNCTIONS LIKE 'A%';

     name      |     type
---------------+---------------
AVG            |AGGREGATE
ABS            |SCALAR
ACOS           |SCALAR
ASIN           |SCALAR
ATAN           |SCALAR
ATAN2          |SCALAR
ASCII          |SCALAR

Or of course, a variation of the above:

SHOW FUNCTIONS LIKE '%DAY%';

     name      |     type
---------------+---------------
DAY            |SCALAR
DAYNAME        |SCALAR
DAYOFMONTH     |SCALAR
DAYOFWEEK      |SCALAR
DAYOFYEAR      |SCALAR
DAY_NAME       |SCALAR
DAY_OF_MONTH   |SCALAR
DAY_OF_WEEK    |SCALAR
DAY_OF_YEAR    |SCALAR
HOUR_OF_DAY    |SCALAR
MINUTE_OF_DAY  |SCALAR