 # Functions + Operators

## Functions

### Generic

Generic functions can typically take arguments of any type.

Function Computes
``CAST (cast_expr) -> T``
Value as type `T` (docs)
``coalesce(x: T...) -> T?``
First non-NULL arg, or NULL if all are NULL
``nullif(x: T, y: T) -> T?``
NULL if `x == y`, else `x`

### Aggregate

Aggregate functions take one or more of the same element type as arguments.

Function Computes
``avg(x: T) -> U``
Average of `T`'s values.

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
``count(x: T) -> int``
Number of non-NULL inputs.
``jsonb_agg(expression) -> jsonb``
Aggregate values (including nulls) as a jsonb array. (docs)
``max(x: T) -> T``
Maximum value among `T`
``min(x: T) -> T``
Minimum value among `T`
``stddev(x: T) -> U``
Historical alias for `stddev_samp`. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
``stddev_pop(x: T) -> U``
Population standard deviation of `T`'s values. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
``stddev_samp(x: T) -> U``
Sample standard deviation of `T`'s values. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same typ as `x`.
``sum(x: T) -> T``
Sum of `T`'s values
``variance(x: T) -> U``
Historical alias for `variance_samp`. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
``variance_pop(x: T) -> U``
Population variance of `T`'s values. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
``variance_samp(x: T) -> U``
Sample variance of `T`'s values. (imprecise)

Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.

### Numbers

Number functions take number-like arguments, e.g. `int`, `float`, `numeric`.

Function Computes
``abs(x: N) -> N``
The absolute value of `x`
``ceil(x: N) -> N``
The largest integer >= `x`
``floor(x: N) -> N``
The largest integer <= `x`
``mod(x: N, y: N) -> N``
`x % y`
``round(x: N) -> N``
`x` rounded to the nearest whole number; halves are rounded up
``round(x: numeric, y: int) -> numeric``
`x` rounded to `y` decimal places, while retaining the same `numeric` scale; halves are rounded up

### String

Function Computes
``ascii(s: str) -> int``
The ASCII value of `s`'s left-most character
``btrim(s: str) -> str``
Trim all spaces from both sides of `s`.
``btrim(s: str, c: str) -> str``
Trim any character in `c` from both sides of `s`.
``bit_length(s: str) -> int``
Number of bits in `s`
``bit_length(b: bytea) -> int``
Number of bits in `b`
``char_length(s: str) -> int``
Number of code points in `s`
``length(s: str) -> int``
Number of code points in `s` (docs)
``length(b: bytea) -> int``
Number of bytes in `s` (docs)
``length(s: bytea, encoding_name: str) -> int``
Number of code points in `s` after encoding (docs)
``lpad(s: str, len: int) -> str``
Prepend `s` with spaces up to length `len`, or right truncate if `len` is less than the length of `s`.
``lpad(s: str, len: int, p: str) -> str``
Prepend `s` with characters pulled from `p` up to length `len`, or right truncate if `len` is less than the length of `s`.
``ltrim(s: str) -> str``
Trim all spaces from the left side of `s`.
``ltrim(s: str, c: str) -> str``
Trim any character in `c` from the left side of `s`.
``octet_length(s: str) -> int``
Number of bytes in `s`
``octet_length(b: bytea) -> int``
Number of bytes in `b`
``regexp_extract(regex: str, haystack: str) -> Col<string>``
Values of the capture groups of `regex` as matched in `haystack`
``replace(s: str, f: str, r: str) -> str``
`s` with all instances of `f` replaced with `r`
``rtrim(s: str) -> str``
Trim all spaces from the right side of `s`.
``rtrim(s: str, c: str) -> str``
Trim any character in `c` from the right side of `s`.
``split_part(s: str, d: s, i: int) -> str``
Split `s` on delimiter `d`. Return the `str` at index `i`, counting from 1.
``substring(s: str, start_pos: int) -> str``
Substring of `s` starting at `start_pos` (docs)
``substring(s: str, start_pos: int, l: int) -> str``
Substring starting at `start_pos` of length `l` (docs)
``trim([BOTH | LEADING | TRAILING]? 'c'? FROM 's') -> str``
Trims any character in `c` from `s` on the specified side.

Defaults:
• Side: `BOTH`
`'c'`: `' '` (space)

### Scalar

Scalar functions take a list of scalar expressions

Function Computes
``expression bool_op ALL(s: Scalars) -> bool``
`true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
``expression bool_op ANY(s: Scalars) -> bool``
`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
``expression IN(s: Scalars) -> bool``
`true` for each value in `expression` if it matches at least one element of `s`
``expression NOT IN(s: Scalars) -> bool``
`true` for each value in `expression` if it does not match any elements of `s`
``expression bool_op SOME(s: Scalars) -> bool``
`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

### Subquery

Subquery functions take a query, e.g. `SELECT`

Function Computes
``expression bool_op ALL(s: Query) -> bool``
`s` must return exactly one column; `true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
``expression bool_op ANY(s: Query) -> bool``
`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
``EXISTS(s: Query) -> bool``
`true` if `s` returns at least one row
``expression IN(s: Query) -> bool``
`s` must return exactly one column; `true` for each value in `expression` if it matches at least one element of `s`
``NOT EXISTS(s: Query) -> bool``
`true` if `s` returns zero rows
``expression NOT IN(s: Query) -> bool``
`s` must return exactly one column; `true` for each value in `expression` if it does not match any elements of `s`
``expression bool_op SOME(s: Query) -> bool``
`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

### Time

Time functions take or produce a time-like type, e.g. `date`, `timestamp`, `timestamptz`.

Function Computes
``current_timestamp() -> timestamptz``
The `timestamptz` representing when the query was executed.

NOTE: Users cannot define views with queries containing `current_timestamp()`.
``date_trunc(time_component: str, val: timestamp) -> timestamp``
Largest `time_component` <= `val` (docs)
``EXTRACT(extract_expr) -> float``
Specified time component from value (docs)
``mz_logical_timestamp() -> numeric``
The logical time at which a query executes.

NOTE: Users cannot define views with queries containing `mz_logical_timestamp()`.
``now() -> timestamptz``
The `timestamptz` representing when the query was executed.

NOTE: Users cannot define views with queries containing `now()`.
``to_timestamp(val: double precision) -> timestamptz``
Converts Unix epoch (seconds since 00:00:00 UTC on January 1, 1970) to timestamp

### UUID

Function Computes
``mz_cluster_id() -> uuid``
The `uuid` uniquely identifying this Materialize cluster.

### JSON

Function Computes
``jsonb_array_elements(j: jsonb) -> Col<jsonb>``
`j`'s elements if `j` is an array. (docs)
``jsonb_array_elements_text(j: jsonb) -> Col<string>``
`j`'s elements if `j` is an array. (docs)
``jsonb_array_length(j: jsonb) -> int``
Number of elements in `j`'s outermost array. (docs)
``jsonb_build_array(x: ...) -> jsonb``
The elements of `x` in a `jsonb` array. Elements can be of heterogenous types. (docs)
``jsonb_build_object(x: ...) -> jsonb``
The elements of x as a `jsonb` object. The argument list alternates between keys and values. (docs)
``jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>``
`j`'s outermost elements if `j` is an object. (docs)
``jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>``
`j`'s outermost elements if `j` is an object. (docs)
``jsonb_object_keys(j: jsonb) -> Col<string>``
`j`'s outermost keys if `j` is an object. (docs)
``jsonb_pretty(j: jsonb) -> string``
Pretty printed (i.e. indented) `j`. (docs)
``jsonb_typeof(j: jsonb) -> string``
Type of `j`'s outermost value. One of `object`, `array`, `string`, `number`, `boolean`, and `null`. (docs)
``jsonb_strip_nulls(j: jsonb) -> jsonb``
`j` with all object fields with a value of `null` removed. Other `null` values remain. (docs)
``to_jsonb(v: T) -> jsonb``
`v` as `jsonb` (docs)

### Table

Table functions evaluate to a set of rows, rather than a single expression.

Function Computes
``generate_series (start: int, stop: int) -> Col<int>``
Generate all integer values between `start` and `stop`, inclusive.

## Operators

### Generic

Operator Computes
`val::type` Cast of `val` as `type` (docs)

### Boolean

Operator Computes
`AND` Boolean “and”
`OR` Boolean “or”
`=` Equality
`<>` Inequality
`!=` Inequality
`<` Less than
`>` Greater than
`<=` Less than or equal to
`>=` Greater than or equal to
`a BETWEEN x AND y` `a >= x AND a <= y`
`a NOT BETWEEN x AND y` `a < x OR a > y`
`a IS NULL` `a = NULL`
`a IS NOT NULL` `a != NULL`
`a LIKE match_expr` `a` matches `match_expr`, using SQL LIKE matching

### Numbers

Operator Computes
`+` Addition
`-` Subtraction
`*` Multiplication
`/` Division
`%` Modulo

### String

Operator Computes
`||` Concatenation
`~` Matches regular expression, case sensitive
`~*` Matches regular expression, case insensitive
`!~` Does not match regular expression, case insensitive
`!~*` Does not match regular expression, case insensitive

The regular expression syntax supported by Materialize is documented by the Rust `regex` crate.

WARNING! Materialize regular expressions are similar to, but not identical to, PostgreSQL regular expressions.

### Time-like

Operation Computes
`date` `+` `interval` `timestamp`
`date` `-` `interval` `timestamp`
`date` `+` `time` `timestamp`
`date` `-` `date` `interval`
`timestamp` `+` `interval` `timestamp`
`timestamp` `-` `interval` `timestamp`
`timestamp` `-` `timestamp` `interval`
`time` `+` `interval` `time`
`time` `-` `interval` `time`
`time` `-` `time` `interval`

### JSON

Operator RHS Type Description
`->` `string`, `int` Access field by name or index position, and return `jsonb` (docs)
`->>` `string`, `int` Access field by name or index position, and return `string` ([docs]/sql/types/jsonb/#field-access-as-string–))
`||` `jsonb` Concatenate LHS and RHS (docs)
`-` `string` Delete all values with key of RHS (docs)
`@>` `jsonb` Does element contain RHS? (docs)
`<@` `jsonb` Does RHS contain element? (docs)
`?` `string` Is RHS a top-level key? (docs)