Materialize/doc/user/data/sql_funcs.yml

- type: Generic
  description: Generic functions can typically take arguments of any type.
  functions:
  - signature: CAST (cast_expr) -> T
    description: Value as type `T`
    url: /sql/functions/cast

  - signature: 'coalesce(x: T...) -> T?'
    description: First non-_NULL_ arg, or _NULL_ if all are _NULL_.

  - signature: 'greatest(x: T...) -> T?'
    description: The maximum argument, or _NULL_ if all are _NULL_.

  - signature: 'least(x: T...) -> T?'
    description: The minimum argument, or _NULL_ if all are _NULL_.

  - signature: 'nullif(x: T, y: T) -> T?'
    description: _NULL_ if `x == y`, else `x`.

- type: Aggregate
  description: Aggregate functions take one or more of the same element type as arguments.
  functions:
  - signature: 'array_agg(x: T) -> T[]'
    description: Aggregate values (including nulls) as an array
    url: /sql/functions/array_agg

  - signature: 'avg(x: T) -> U'
    description: |
      Average of `T`'s values.

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

  - signature: 'bool_and(x: T) -> T'
    description: _NULL_ if all values of `x` are _NULL_, otherwise true if all values of `x` are true, otherwise false.

  - signature: 'bool_or(x: T) -> T'
    description: _NULL_ if all values of `x` are _NULL_, otherwise true if any values of `x` are true, otherwise false.

  - signature: 'count(x: T) -> bigint'
    description: Number of non-_NULL_ inputs.

  - signature: jsonb_agg(expression) -> jsonb
    description: Aggregate values (including nulls) as a jsonb array
    url: /sql/functions/jsonb_agg

  - signature: jsonb_object_agg(keys, values) -> jsonb
    description: Aggregate keys and values (including nulls) as a jsonb object
    url: /sql/functions/jsonb_object_agg

  - signature: 'max(x: T) -> T'
    description: Maximum value among `T`.

  - signature: 'min(x: T) -> T'
    description: Minimum value among `T`.

  - signature: 'stddev(x: T) -> U'
    description: |
      Historical alias for `stddev_samp`. *(imprecise)*

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

  - signature: 'stddev_pop(x: T) -> U'
    description: |
      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`.

  - signature: 'stddev_samp(x: T) -> U'
    description: |
      Sample standard deviation of `T`'s values. *(imprecise)*

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

  - signature: 'string_agg(value: text, delimiter: text) -> text'
    description: Concatenates the non-null input values into text. Each value after the first is preceded by the corresponding delimiter
    url: /sql/functions/string_agg

  - signature: 'sum(x: T) -> U'
    description: |
      Sum of `T`'s values

      Returns `bigint` if `x` is `int` or `smallint`, `numeric` if `x` is `bigint` or `uint8`,
      `uint8` if `x` is `uint4` or `uint2`, else returns same type as `x`.

  - signature: 'variance(x: T) -> U'
    description: |
      Historical alias for `var_samp`. *(imprecise)*

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

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

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

- type: List
  description: List functions take [`list`](../types/list) arguments, and are [polymorphic](../types/list/#polymorphism).
  functions:
    - signature: 'list_agg(x: any) -> L'
      description: Aggregate values (including nulls) as a list
      url: /sql/functions/list_agg

    - signature: 'list_append(l: listany, e: listelementany) -> L'
      description: Appends `e` to `l`.

    - signature: 'list_cat(l1: listany, l2: listany) -> L'
      description: Concatenates `l1` and `l2`.

    - signature: 'list_length(l: listany) -> int'
      description: Return the number of elements in `l`.

    - signature: 'list_prepend(e: listelementany, l: listany) -> listany'
      description: Prepends `e` to `l`.

- type: Map
  description: Map functions take [`map`](../types/map) arguments, and are [polymorphic](../types/#polymorphism).
  functions:
    - signature: 'map_length(m: mapany) -> int'
      description: Return the number of elements in `m`.
    - signature: 'map_build(kvs: list record(text, T)) -> map[text=>T]'
      description: |
        Builds a map from a list of records whose fields are two elements, the
        first of which is `text`. In the face of duplicate keys, `map_build` retains
        value from the record in the latest positition. This function is
        purpose-built to process [Kafka headers](/sql/create-source/kafka/#headers).
      version-added: v0.86
    - signature: 'map_agg(keys: text, values: T) -> map[text=>T]'
      description: Aggregate keys and values (including nulls) as a map
      url: /sql/functions/map_agg

- type: Numbers
  description: Number functions take number-like arguments, e.g. [`int`](../types/int),
    [`float`](../types/float), [`numeric`](../types/numeric), unless otherwise specified.
  functions:
  - signature: 'abs(x: N) -> N'
    description: The absolute value of `x`.

  - signature: 'cbrt(x: double precision) -> double precision'
    description: The cube root of `x`.

  - signature: 'ceil(x: N) -> N'
    description: The smallest integer >= `x`.

  - signature: 'ceiling(x: N) -> N'
    description: "Alias of `ceil`."

  - signature: 'exp(x: N) -> N'
    description: Exponential of `x` (e raised to the given power)

  - signature: 'floor(x: N) -> N'
    description: The largest integer <= `x`.

  - signature: 'ln(x: double precision) -> double precision'
    description: Natural logarithm of `x`.

  - signature: 'ln(x: numeric) -> numeric'
    description: Natural logarithm of `x`.

  - signature: 'log(x: double precision) -> double precision'
    description: Base 10 logarithm of `x`.

  - signature: 'log(x: numeric) -> numeric'
    description: Base 10 logarithm of `x`.

  - signature: 'log10(x: double precision) -> double precision'
    description: Base 10 logarithm of `x`, same as `log`.

  - signature: 'log10(x: numeric) -> numeric'
    description: Base 10 logarithm of `x`, same as `log`.

  - signature: 'log(b: numeric, x: numeric) -> numeric'
    description: Base `b` logarithm of `x`.

  - signature: 'mod(x: N, y: N) -> N'
    description: "`x % y`"

  - signature: 'pow(x: double precision, y: double precision) -> double precision'
    description: "Alias of `power`."

  - signature: 'pow(x: numeric, y: numeric) -> numeric'
    description: "Alias of `power`."

  - signature: 'power(x: double precision, y: double precision) -> double precision'
    description: "`x` raised to the power of `y`."

  - signature: 'power(x: numeric, y: numeric) -> numeric'
    description: "`x` raised to the power of `y`."

  - signature: 'round(x: N) -> N'
    description: |
      `x` rounded to the nearest whole number.
      If `N` is `real` or `double precision`, rounds ties to the nearest even number.
      If `N` is `numeric`, rounds ties away from zero.

  - signature: 'round(x: numeric, y: int) -> numeric'
    description: "`x` rounded to `y` decimal places, while retaining the same
      [`numeric`](../types/numeric) scale; rounds ties away from zero."

  - signature: 'sqrt(x: numeric) -> numeric'
    description: The square root of `x`.

  - signature: 'sqrt(x: double precision) -> double precision'
    description: The square root of `x`.

  - signature: 'trunc(x: N) -> N'
    description: "`x` truncated toward zero to a whole number."

- type: Trigonometric
  description: Trigonometric functions take and return `double precision` values.
  functions:
  - signature: 'cos(x: double precision) -> double precision'
    description: The cosine of `x`, with `x` in radians.

  - signature: 'acos(x: double precision) -> double precision'
    description: The inverse cosine of `x`, result in radians.

  - signature: 'cosh(x: double precision) -> double precision'
    description: The hyperbolic cosine of `x`, with `x` as a hyperbolic angle.

  - signature: 'acosh(x: double precision) -> double precision'
    description: The inverse hyperbolic cosine of `x`.

  - signature: 'cot(x: double precision) -> double precision'
    description: The cotangent of `x`, with `x` in radians.

  - signature: 'sin(x: double precision) -> double precision'
    description: The sine of `x`, with `x` in radians.

  - signature: 'asin(x: double precision) -> double precision'
    description: The inverse sine of `x`, result in radians.

  - signature: 'sinh(x: double precision) -> double precision'
    description: The hyperbolic sine of `x`, with `x` as a hyperbolic angle.

  - signature: 'asinh(x: double precision) -> double precision'
    description: The inverse hyperbolic sine of `x`.

  - signature: 'tan(x: double precision) -> double precision'
    description: The tangent of `x`, with `x` in radians.

  - signature: 'atan(x: double precision) -> double precision'
    description: The inverse tangent of `x`, result in radians.

  - signature: 'tanh(x: double precision) -> double precision'
    description: The hyperbolic tangent of `x`, with `x` as a hyperbolic angle.

  - signature: 'atanh(x: double precision) -> double precision'
    description: The inverse hyperbolic tangent of `x`.

  - signature: 'radians(x: double precision) -> double precision'
    description: Converts degrees to radians.

  - signature: 'degrees(x: double precision) -> double precision'
    description: Converts radians to degrees.

- type: String
  functions:
  - signature: 'ascii(s: str) -> int'
    description: The ASCII value of `s`'s left-most character.

  - signature: 'btrim(s: str) -> str'
    description: Trim all spaces from both sides of `s`.

  - signature: 'btrim(s: str, c: str) -> str'
    description: Trim any character in `c` from both sides of `s`.

  - signature: 'bit_count(b: bytea) -> int'
    description: Returns the number of bits set in the bit string (aka _popcount_).

  - signature: 'bit_length(s: str) -> int'
    description: Number of bits in `s`.

  - signature: 'bit_length(b: bytea) -> int'
    description: Number of bits in `b`.

  - signature: 'char_length(s: str) -> int'
    description: Number of code points in `s`.

  - signature: 'chr(i: int) -> str'
    description: |
      Character with the given Unicode codepoint.
      Only supports codepoints that can be encoded in UTF-8.
      The NULL (0) character is not allowed.

  - signature: 'concat(f: any, r: any...) -> text'
    description: Concatenates the text representation of non-NULL arguments.

  - signature: 'concat_ws(sep: str, f: any, r: any...) -> text'
    description: Concatenates the text representation of non-NULL arguments from `f` and `r` separated by `sep`.

  - signature: 'convert_from(b: bytea, src_encoding: text) -> text'
    description: Convert data `b` from original encoding specified by `src_encoding` into `text`.

  - signature: 'decode(s: text, format: text) -> bytea'
    description: Decode `s` using the specified textual representation
    url: /sql/functions/encode

  - signature: 'encode(b: bytea, format: text) -> text'
    description: Encode `b` using the specified textual representation
    url: /sql/functions/encode

  - signature: 'get_bit(b: bytea, n: int) -> int'
    description: Return the `n`th bit from `b`, where the left-most bit in `b` is at the 0th position.

  - signature: 'get_byte(b: bytea, n: int) -> int'
    description: Return the `n`th byte from `b`, where the left-most byte in `b` is at the 0th position.

  - signature: 'constant_time_eq(a: bytea, b: bytea) -> bool'
    description: Returns `true` if the arrays are identical, otherwise returns `false`. The implementation mitigates timing attacks by making a best-effort attempt to execute in constant time if the arrays have the same length, regardless of their contents.

  - signature: 'constant_time_eq(a: text, b: text) -> bool'
    description: Returns `true` if the strings are identical, otherwise returns `false`. The implementation mitigates timing attacks by making a best-effort attempt to execute in constant time if the strings have the same length, regardless of their contents.

  - signature: 'initcap(a: text) -> text'
    description: |
      Returns `a` with the first character of every word in upper case and all
      other characters in lower case. Words are separated by non-alphanumeric
      characters.
    version-added: v0.97

  - signature: 'left(s: str, n: int) -> str'
    description: The first `n` characters of `s`. If `n` is negative, all but the last `|n|` characters of `s`.

  - signature: 'length(s: str) -> int'
    description: Number of code points in `s`.
    url: /sql/functions/length

  - signature: 'length(b: bytea) -> int'
    description: Number of bytes in `s`.
    url: /sql/functions/length

  - signature: 'length(s: bytea, encoding_name: str) -> int'
    description: Number of code points in `s` after encoding
    url: /sql/functions/length

  - signature: 'lower(s: str) -> str'
    description: Convert `s` to lowercase.

  - signature: 'lpad(s: str, len: int) -> str'
    description: "Prepend `s` with spaces up to length `len`,
      or right truncate if `len` is less than the length of `s`."

  - signature: 'lpad(s: str, len: int, p: str) -> str'
    description: "Prepend `s` with characters pulled from `p` up to length `len`,
      or right truncate if `len` is less than the length of `s`."

  - signature: 'ltrim(s: str) -> str'
    description: Trim all spaces from the left side of `s`.

  - signature: 'ltrim(s: str, c: str) -> str'
    description: Trim any character in `c` from the left side of `s`.

  - signature: 'octet_length(s: str) -> int'
    description: Number of bytes in `s`.

  - signature: 'octet_length(b: bytea) -> int'
    description: Number of bytes in `b`.

  - signature: 'parse_ident(ident: str[, strict_mode: bool]) -> str[]'
    description: |
      Given a qualified identifier like `a."b".c`, splits into an array of the
      constituent identifiers with quoting removed and escape sequences decoded.
      Extra characters after the last identifier are ignored unless the
      `strict_mode` parameter is `true` (defaults to `false`).

  - signature: 'position(sub: str IN s: str) -> int'
    description: The starting index of `sub` within `s` or `0` if `sub` is not a substring of `s`.

  - signature: 'regexp_match(haystack: str, needle: str [, flags: str]]) -> str[]'
    description: |
      Matches the regular expression `needle` against haystack, returning a
      string array that contains the value of each capture group specified in
      `needle`, in order. If `flags` is set to the string `i` matches
      case-insensitively.

  - signature: 'regexp_replace(source: str, pattern: str, replacement: str [, flags: str]]) -> str'
    description: |
      Replaces the first occurrence of `pattern` with `replacement` in `source`.
      No match will return `source` unchanged.

      If `flags` is set to `g`, all occurrences are replaced.
      If `flags` is set to `i`, matches case-insensitively.

      `$N` or `$name` in `replacement` can be used to match capture groups.
      `${N}` must be used to disambiguate capture group indexes from names if other characters follow `N`.
      A `$$` in `replacement` will write a literal `$`.

      See the [rust regex docs](https://docs.rs/regex/latest/regex/struct.Regex.html#method.replace) for more details about replacement.

  - signature: "regexp_matches(haystack: str, needle: str [, flags: str]]) -> str[]"
    description: |
      Matches the regular expression `needle` against haystack, returning a
      string array that contains the value of each capture group specified in
      `needle`, in order. If `flags` is set to the string `i` matches
      case-insensitively. If `flags` is set to the string `g` all matches are
      returned, otherwise only the first match is returned. Without the `g`
      flag, the behavior is the same as `regexp_match`.

  - signature: 'regexp_split_to_array(text: str, pattern: str [, flags: str]]) -> str[]'
    description: |
      Splits `text` by the regular expression `pattern` into an array.
      If `flags` is set to `i`, matches case-insensitively.

  - signature: 'repeat(s: str, n: int) -> str'
    description: Replicate the string `n` times.

  - signature: 'replace(s: str, f: str, r: str) -> str'
    description: "`s` with all instances of `f` replaced with `r`."

  - signature: 'right(s: str, n: int) -> str'
    description: The last `n` characters of `s`. If `n` is negative, all but the first `|n|` characters of `s`.

  - signature: 'rtrim(s: str) -> str'
    description: Trim all spaces from the right side of `s`.

  - signature: 'rtrim(s: str, c: str) -> str'
    description: Trim any character in `c` from the right side of `s`.

  - signature: 'split_part(s: str, d: s, i: int) -> str'
    description: Split `s` on delimiter `d`. Return the `str` at index `i`, counting from 1.

  - signature: 'starts_with(s: str, prefix: str) -> bool'
    description: Report whether `s` starts with `prefix`.

  - signature: 'substring(s: str, start_pos: int) -> str'
    description: Substring of `s` starting at `start_pos`
    url: /sql/functions/substring

  - signature: 'substring(s: str, start_pos: int, l: int) -> str'
    description: Substring starting at `start_pos` of length `l`
    url: /sql/functions/substring

  - signature: "substring('s' [FROM 'start_pos']? [FOR 'l']?) -> str"
    description: Substring starting at `start_pos` of length `l`
    url: /sql/functions/substring

  - signature: "translate(s: str, from: str, to: str) -> str"
    description: |
      Any character in `s` that matches a character in `from` is replaced by the corresponding character in `to`.
      If `from` is longer than `to`, occurrences of the extra characters in `from` are removed.

  - signature: "trim([BOTH | LEADING | TRAILING]? ['c'? FROM]? 's') -> str"
    description: |
      Trims any character in `c` from `s` on the specified side.

      Defaults:

      &bull; Side: `BOTH`

      &bull; `'c'`: `' '` (space)

  - signature: 'try_parse_monotonic_iso8601_timestamp(s: str) -> timestamp'
    description: |
      Parses a specific subset of ISO8601 timestamps, returning `NULL` instead of
      error on failure: `YYYY-MM-DDThh:mm:ss.sssZ`
    url: /sql/functions/pushdown

  - signature: 'upper(s: str) -> str'
    description: Convert `s` to uppercase.

  - signature: 'reverse(s: str) -> str'
    description: Reverse the characters in `s`.

  - signature: 'string_to_array(s: str, delimiter: str [, null_string: str]) -> str[]'
    description: |
      Splits the string at occurrences of delimiter and returns a text array of
      the split segments.

      If `delimiter` is NULL, each character in the string will become a
      separate element in the array.

      If `delimiter` is an empty string, then the string is treated as a single
      field.

      If `null_string` is supplied and is not NULL, fields matching that string
      are replaced by NULL.

      For example: `string_to_array('xx~~yy~~zz', '~~', 'yy')` → `{xx,NULL,zz}`

- type: Scalar
  description: Scalar functions take a list of scalar expressions
  functions:
  - signature: 'expression bool_op ALL(s: Scalars) -> bool'
    description: "`true` if applying [bool_op](#boolean-operators) to `expression` and every
      value of `s` evaluates to `true`."

  - signature: 'expression bool_op ANY(s: Scalars) -> bool'
    description: |

      `true` if applying [bool_op](#boolean-operators) to `expression` and any
      value of `s` evaluates to `true`. Avoid using in equi-join conditions as
      its use in the equi-join condition can lead to a significant increase in
      memory usage. See [idiomatic Materialize
      SQL](/transform-data/idiomatic-materialize-sql/any) for the alternative.

  - signature: 'expression IN(s: Scalars) -> bool'
    description: "`true` for each value in `expression` if it matches at least one
      element of `s`."

  - signature: 'expression NOT IN(s: Scalars) -> bool'
    description: "`true` for each value in `expression` if it does not match any elements
      of `s`."

  - signature: 'expression bool_op SOME(s: Scalars) -> bool'
    description: "`true` if applying [bool_op](#boolean-operators) to `expression` and any value
      of `s` evaluates to `true`."

- type: Subquery
  description: Subquery functions take a query, e.g. [`SELECT`](/sql/select)
  functions:
  - signature: 'expression bool_op ALL(s: Query) -> bool'
    description: "`s` must return exactly one column; `true` if applying [bool_op](#boolean-operators)
      to `expression` and every value of `s` evaluates to `true`."

  - signature: 'expression bool_op ANY(s: Query) -> bool'
    description: "`s` must return exactly one column; `true` if applying [bool_op](#boolean-operators)
      to `expression` and any value of `s` evaluates to `true`."

  - signature: 'csv_extract(num_csv_col: int, col_name: string) -> col1: string, ... coln: string'
    description: Extracts separated values from a column containing a CSV file formatted as a string
    url: /sql/functions/csv_extract

  - signature: 'EXISTS(s: Query) -> bool'
    description: "`true` if `s` returns at least one row."

  - signature: 'expression IN(s: Query) -> bool'
    description: "`s` must return exactly one column; `true` for each value in `expression`
      if it matches at least one element of `s`."

  - signature: 'NOT EXISTS(s: Query) -> bool'
    description: "`true` if `s` returns zero rows."

  - signature: 'expression NOT IN(s: Query) -> bool'
    description: "`s` must return exactly one column; `true` for each value in `expression`
      if it does not match any elements of `s`."

  - signature: 'expression bool_op SOME(s: Query) -> bool'
    description: "`s` must return exactly one column; `true` if applying [bool_op](#boolean-operators)
      to `expression` and any value of `s` evaluates to `true`."

- type: Date and time
  description: Time functions take or produce a time-like type, e.g. [`date`](../types/date),
    [`timestamp`](../types/timestamp), [`timestamp with time zone`](../types/timestamptz).
  functions:
  - signature: 'age(timestamp, timestamp) -> interval'
    description: 'Subtracts one timestamp from another, producing a "symbolic" result that uses years and months, rather than just days.'

  - signature: current_timestamp() -> timestamptz
    description: 'The `timestamp with time zone` representing when the query was executed.'
    unmaterializable: true

  - signature: 'date_bin(stride: interval, source: timestamp, origin: timestamp) -> timestamp'
    description: Align `source` with `origin` along `stride`
    url: /sql/functions/date-bin

  - signature: 'date_trunc(time_component: str, val: timestamp) -> timestamp'
    description: Largest `time_component` <= `val`
    url: /sql/functions/date-trunc

  - signature: 'date_trunc(time_component: str, val: interval) -> interval'
    description: Largest `time_component` <= `val`
    url: /sql/functions/date-trunc

  - signature: EXTRACT(extract_expr) -> numeric
    description: Specified time component from value
    url: /sql/functions/extract

  - signature: 'date_part(time_component: str, val: timestamp) -> float'
    description: Specified time component from value
    url: /sql/functions/date-part

  - signature: mz_now() -> mz_timestamp
    description: |
      The logical time at which a query executes. Used for temporal filters and query timestamp introspection
    url: /sql/functions/now_and_mz_now
    unmaterializable_unless_temporal_filter: true

  - signature: now() -> timestamptz
    description: 'The `timestamp with time zone` representing when the query was executed'
    url: /sql/functions/now_and_mz_now
    unmaterializable: true

  - signature: timestamp AT TIME ZONE zone -> timestamptz
    description: 'Converts `timestamp` to the specified time zone, expressed as an offset from UTC'
    url: /sql/functions/timezone-and-at-time-zone
    known_time_zone_limitation_cast: true

  - signature: timestamptz AT TIME ZONE zone -> timestamp
    description: 'Converts `timestamp with time zone` from UTC to the specified time zone, expressed as the local time'
    url: /sql/functions/timezone-and-at-time-zone
    known_time_zone_limitation_cast: true

  - signature: timezone(zone, timestamp) -> timestamptz
    description: 'Converts `timestamp` to specified time zone, expressed as an offset from UTC'
    url: /sql/functions/timezone-and-at-time-zone
    known_time_zone_limitation_cast: true

  - signature: timezone(zone, timestamptz) -> timestamp
    description: 'Converts `timestamp with time zone` from UTC to specified time zone, expressed as the local time'
    url: /sql/functions/timezone-and-at-time-zone
    known_time_zone_limitation_cast: true

  - signature: |-
      timezone_offset(zone: str, when: timestamptz) ->
      (abbrev: str, base_utc_offset: interval, dst_offset: interval)
    description: |
      Describes a time zone's offset from UTC at a specified moment.

      `zone` must be a valid IANA Time Zone Database identifier.

      `when` is a `timestamp with time zone` that specifies the moment at which to determine `zone`'s offset from UTC.

      `abbrev` is the abbreviation for `zone` that is in use at the specified moment (e.g., `EST` or `EDT`).

      `base_utc_offset` is the base offset from UTC at the specified moment (e.g., `-5 hours`). Positive offsets mean east of Greenwich; negative offsets mean west of Greenwich.

      `dst_offset` is the additional offset at the specified moment due to Daylight Saving Time rules (e.g., `1 hours`). If non-zero, Daylight Saving Time is in effect.

  - signature: 'to_timestamp(val: double precision) -> timestamptz'
    description: Converts Unix epoch (seconds since 00:00:00 UTC on January 1, 1970)
      to timestamp.

  - signature: 'to_char(val: timestamp, format: str)'
    description: Converts a timestamp into a string using the specified format
    url: /sql/functions/to_char

  - signature: 'justify_days(val: interval) -> interval'
    description: Adjust interval so 30-day time periods are represented as months
    url: /sql/functions/justify-days

  - signature: 'justify_hours(val: interval) -> interval'
    description: Adjust interval so 24-hour time periods are represented as days
    url: /sql/functions/justify-hours

  - signature: 'justify_interval(val: interval) -> interval'
    description: Adjust interval using justify_days and justify_hours, with additional sign adjustments
    url: /sql/functions/justify-interval

- type: UUID
  functions:

  - signature: 'uuid_generate_v5(namespace: uuid, name: text) -> uuid'
    description: 'Generates a [version 5 UUID](https://www.rfc-editor.org/rfc/rfc4122#page-7) (SHA-1) in the given namespace using
      the specified input name.'

- type: JSON
  functions:
  - signature: jsonb_agg(expression) -> jsonb
    description: Aggregate values (including nulls) as a jsonb array
    url: /sql/functions/jsonb_agg

  - signature: 'jsonb_array_elements(j: jsonb) -> Col<jsonb>'
    description: "`j`'s elements if `j` is an array"
    url: /sql/types/jsonb#jsonb_array_elements

  - signature: 'jsonb_array_elements_text(j: jsonb) -> Col<string>'
    description: "`j`'s elements if `j` is an array"
    url: /sql/types/jsonb#jsonb_array_elements_text

  - signature: 'jsonb_array_length(j: jsonb) -> int'
    description: Number of elements in `j`'s outermost array
    url: /sql/types/jsonb#jsonb_array_length

  - signature: 'jsonb_build_array(x: ...) -> jsonb'
    description: Output each element of `x` as a `jsonb` array. Elements can be of heterogenous
      types
    url: /sql/types/jsonb#jsonb_build_array

  - signature: 'jsonb_build_object(x: ...) -> jsonb'
    description: The elements of x as a `jsonb` object. The argument list alternates
      between keys and values
    url: /sql/types/jsonb#jsonb_build_object

  - signature: 'jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>'
    description: "`j`'s outermost elements if `j` is an object"
    url: /sql/types/jsonb#jsonb_each

  - signature: 'jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>'
    description: "`j`'s outermost elements if `j` is an object"
    url: /sql/types/jsonb#jsonb_each_text

  - signature: jsonb_object_agg(keys, values) -> jsonb
    description: Aggregate keys and values (including nulls) as a `jsonb` object
    url: /sql/functions/jsonb_object_agg

  - signature: 'jsonb_object_keys(j: jsonb) -> Col<string>'
    description: "`j`'s outermost keys if `j` is an object"
    url: /sql/types/jsonb#jsonb_object_keys

  - signature: 'jsonb_pretty(j: jsonb) -> string'
    description: Pretty printed (i.e. indented) `j`
    url: /sql/types/jsonb#jsonb_pretty

  - signature: 'jsonb_typeof(j: jsonb) -> string'
    description: Type of `j`'s outermost value. One of `object`, `array`, `string`,
      `number`, `boolean`, and `null`
    url: /sql/types/jsonb#jsonb_typeof

  - signature: 'jsonb_strip_nulls(j: jsonb) -> jsonb'
    description: "`j` with all object fields with a value of `null` removed. Other
      `null` values remain"
    url: /sql/types/jsonb#jsonb_strip_nulls

  - signature: 'to_jsonb(v: T) -> jsonb'
    description: "`v` as `jsonb`"
    url: /sql/types/jsonb#to_jsonb

- type: Table
  description: Table functions evaluate to a set of rows, rather than a single expression.
  functions:
  - signature: 'generate_series(start: int, stop: int) -> Col<int>'
    description: Generate all integer values between `start` and `stop`, inclusive.
  - signature: 'generate_series(start: int, stop: int, step: int) -> Col<int>'
    description: Generate all integer values between `start` and `stop`, inclusive, incrementing
      by `step` each time.
  - signature: 'generate_series(start: timestamp, stop: timestamp, step: interval) -> Col<timestamp>'
    description: Generate all timestamp values between `start` and `stop`, inclusive, incrementing
      by `step` each time.
  - signature: 'generate_subscripts(a: anyarray, dim: int) -> Col<int>'
    description: Generates a series comprising the valid subscripts of the `dim`'th dimension of the given array `a`.
  - signature: 'regexp_extract(regex: str, haystack: str) -> Col<string>'
    description: Values of the capture groups of `regex` as matched in `haystack`.
  - signature: 'regexp_split_to_table(text: str, pattern: str [, flags: str]]) -> Col<string>'
    description: |
      Splits `text` by the regular expression `pattern`.
      If `flags` is set to `i`, matches case-insensitively.
  - signature: 'unnest(a: anyarray)'
    description: Expands the array `a` into a set of rows.
  - signature: 'unnest(l: anylist)'
    description: Expands the list `l` into a set of rows.
  - signature: "unnest(m: anymap)"
    description: Expands the map `m` in a set of rows with the columns `key` and `value`.

- type: Array
  functions:
  - signature: 'array_cat(a1: arrayany, a2: arrayany) -> arrayany'
    description: 'Concatenates `a1` and `a2`.'
  - signature: 'array_fill(anyelement, int[], [, int[]]) -> anyarray'
    description: 'Returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1.'
  - signature: 'array_length(a: arrayany, dim: bigint) -> int'
    description: 'Returns the length of the specified dimension of the array.'
  - signature: 'array_position(haystack: anycompatiblearray, needle: anycompatible) -> int'
    description: 'Returns the subscript of `needle` in `haystack`. Returns `null` if not found.'
  - signature: 'array_position(haystack: anycompatiblearray, needle: anycompatible, skip: int) -> int'
    description: 'Returns the subscript of `needle` in `haystack`, skipping the first `skip` elements. Returns `null` if not found.'
  - signature: 'array_to_string(a: anyarray, sep: text [, ifnull: text]) -> text'
    description: |
      Concatenates the elements of `array` together separated by `sep`.
      Null elements are omitted unless `ifnull` is non-null, in which case
      null elements are replaced with the value of `ifnull`.
  - signature: 'array_remove(a: anyarray, e: anyelement) -> anyarray'
    description: |
      Returns the array `a` without any elements equal to the given value `e`.
      The array must be one-dimensional. Comparisons are done using `IS NOT
      DISTINCT FROM semantics, so it is possible to remove NULLs.

- type: Hash
  functions:
    - signature: 'crc32(data: bytea) -> uint32'
      description: |
        Computes the 32-bit cyclic redundancy check of the given bytea `data` using the IEEE 802.3 polynomial.
      version-added: v0.114
    - signature: 'crc32(data: text) -> uint32'
      description: |
        Computes the 32-bit cyclic redundancy check of the given text `data` using the IEEE 802.3 polynomial.
      version-added: v0.114
    - signature: 'digest(data: text, type: text) -> bytea'
      description: |
        Computes a binary hash of the given text `data` using the specified `type` algorithm.
        Supported hash algorithms are: `md5`, `sha1`, `sha224`, `sha256`, `sha384`, and `sha512`.
    - signature: 'digest(data: bytea, type: text) -> bytea'
      description: |
        Computes a binary hash of the given bytea `data` using the specified `type` algorithm.
        The supported hash algorithms are the same as for the text variant of this function.
    - signature: 'hmac(data: text, key: text, type: text) -> bytea'
      description: |
        Computes a hashed MAC of the given text `data` using the specified `key` and
        `type` algorithm. Supported hash algorithms are the same as for `digest`.
    - signature: 'hmac(data: bytea, key: bytea, type: text) -> bytea'
      description: |
        Computes a hashed MAC of the given bytea `data` using the specified `key` and
        `type` algorithm. The supported hash algorithms are the same as for `digest`.
    - signature: 'kafka_murmur2(data: bytea) -> integer'
      description: |
        Computes the Murmur2 hash of the given bytea `data` using the seed used by Kafka's default partitioner and with the high bit cleared.
      version-added: v0.114
    - signature: 'kafka_murmur2(data: text) -> integer'
      description: |
        Computes the Murmur2 hash of the given text `data` using the seed used by Kafka's default partitioner and with the high bit cleared.
      version-added: v0.114
    - signature: 'md5(data: bytea) -> text'
      description: |
        Computes the MD5 hash of the given bytea `data`.
        For PostgreSQL compatibility, returns a hex-encoded value of type `text` rather than `bytea`.
    - signature: 'seahash(data: bytea) -> u64'
      description: |
        Computes the [SeaHash](https://docs.rs/seahash) hash of the given bytea `data`.
      version-added: v0.114
    - signature: 'seahash(data: text) -> u64'
      description: |
        Computes the [SeaHash](https://docs.rs/seahash) hash of the given text `data`.
      version-added: v0.114
    - signature: 'sha224(data: bytea) -> bytea'
      description: |
        Computes the SHA-224 hash of the given bytea `data`.
    - signature: 'sha256(data: bytea) -> bytea'
      description: |
        Computes the SHA-256 hash of the given bytea `data`.
    - signature: 'sha384(data: bytea) -> bytea'
      description: |
        Computes the SHA-384 hash of the given bytea `data`.
    - signature: 'sha512(data: bytea) -> bytea'
      description: |
        Computes the SHA-512 hash of the given bytea `data`.

- type: Window
  description: |
    {{< tip >}}

    For some window function query patterns, rewriting your query to not use
    window functions can yield better performance.  See [Idiomatic Materialize SQL](/transform-data/idiomatic-materialize-sql/) for details.

    {{</ tip >}}

    Window functions compute values across sets of rows related to the current row.
    For example, you can use a window aggregation to smooth measurement data by computing the average of the last 5
    measurements before every row as follows:

    ```
    SELECT
      avg(measurement) OVER (ORDER BY time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
    FROM measurements;
    ```

    Window functions always need an `OVER` clause. For the `OVER` clause, Materialize supports the same
    [syntax as
    PostgreSQL](https://www.postgresql.org/docs/current/tutorial-window.html),
    but supports only the following frame modes:

    - the `ROWS` frame mode.

    - the default frame, which is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
    ROW`.

    {{< note >}}
    {{% idiomatic-sql/materialize-window-functions %}}

    See [Idiomatic Materialize SQL](/transform-data/idiomatic-materialize-sql/)
    for examples of rewriting window functions.

    {{</ note >}}

    In addition to the below window functions, you can use the `OVER` clause with any [aggregation function](#aggregate-functions)
    (e.g., `sum`, `avg`) as well. Using an aggregation with an `OVER` clause is called a _window aggregation_. A
    window aggregation computes the aggregate not on the groups specified by the `GROUP BY` clause, but on the frames
    specified inside the `OVER` clause. (Note that a window aggregation produces exactly one output value _for each input
    row_. This is different from a standard aggregation, which produces one output value for each _group_ specified by
    the `GROUP BY` clause.)
  functions:
  - signature: 'dense_rank() -> int'
    description: |
      Returns the rank of the current row within its partition without gaps, counting from 1.
      Rows that compare equal will have the same rank.
  - signature: 'first_value(value anycompatible) -> anyelement'
    description: |
      Returns `value` evaluated at the first row of the window frame. The default window frame is
      `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.

      See also [Idiomatic Materialize SQL: First value](/transform-data/idiomatic-materialize-sql/first-value/).

  - signature: 'lag(value anycompatible [, offset integer [, default anycompatible ]]) -> int'
    description: |
      Returns `value` evaluated at the row that is `offset` rows before the current row within the partition;
      if there is no such row, instead returns `default` (which must be of a type compatible with `value`).
      If `offset` is `NULL`, `NULL` is returned instead.
      Both `offset` and `default` are evaluated with respect to the current row.
      If omitted, `offset` defaults to 1 and `default` to `NULL`.

      See also [Idiomatic Materialize SQL: Lag over](/transform-data/idiomatic-materialize-sql/lag/).

  - signature: 'last_value(value anycompatible) -> anyelement'
    description: |
      Returns `value` evaluated at the last row of the window frame. The default window frame is
      `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.

      See also [Idiomatic Materialize SQL: Last
      value](/transform-data/idiomatic-materialize-sql/last-value/).

  - signature: 'lead(value anycompatible [, offset integer [, default anycompatible ]]) -> int'
    description: |
      Returns `value` evaluated at the row that is `offset` rows after the current row within the partition;
      if there is no such row, instead returns `default` (which must be of a type compatible with `value`).
      If `offset` is `NULL`, `NULL` is returned instead.
      Both `offset` and `default` are evaluated with respect to the current row.
      If omitted, `offset` defaults to 1 and `default` to `NULL`.

      See also [Idiomatic Materialize SQL: Lead
      over](/transform-data/idiomatic-materialize-sql/lead/).

  - signature: 'rank() -> int'
    description: |
      Returns the rank of the current row within its partition with gaps (counting from 1):
      rows that compare equal will have the same rank, and then the rank is incremented by the number of rows that
      compared equal.
  - signature: 'row_number() -> int'
    description: |
      Returns the number of the current row within its partition, counting from 1.
      Rows that compare equal will be ordered in an unspecified way.

      See also [Idiomatic Materialize SQL: Top-K](/transform-data/idiomatic-materialize-sql/top-k/).


- type: System information
  description: Functions that return information about the system.
  functions:
  - signature: 'mz_environment_id() -> text'
    description: Returns a string containing a `uuid` uniquely identifying the Materialize environment.
    unmaterializable: true
  - signature: 'mz_uptime() -> interval'
    description: Returns the length of time that the materialized process has been running.
    unmaterializable: true
  - signature: 'mz_version() -> text'
    description: Returns the server's version information as a human-readable string.
    unmaterializable: true
  - signature: 'mz_version_num() -> int'
    description: Returns the server's version as an integer having the format `XXYYYZZ`, where `XX` is the major version, `YYY` is the minor version and `ZZ` is the patch version.
    unmaterializable: true
  - signature: 'current_database() -> text'
    description: |
      Returns the name of the current database.
    unmaterializable: true
  - signature: 'current_catalog() -> text'
    description: |
      Alias for `current_database`.
    unmaterializable: true
    version-added: v0.102
  - signature: 'current_user() -> text'
    description: |
      Returns the name of the user who executed the containing query.
    unmaterializable: true
  - signature: 'current_role() -> text'
    description: |
      Alias for `current_user`.
    unmaterializable: true
    version-added: v0.102
  - signature: 'user() -> text'
    description: |
      Alias for `current_user`.
    unmaterializable: true
    version-added: v0.102
  - signature: 'session_user() -> text'
    description: |
      Returns the name of the user who initiated the database connection.
    unmaterializable: true
  - signature: 'mz_row_size(expr: Record) -> int'
    description: Returns the number of bytes used to store a row.

- type: PostgreSQL compatibility
  description: |
    Functions whose primary purpose is to facilitate compatibility with PostgreSQL tools.
    These functions may have suboptimal performance characteristics.
  functions:
  - signature: 'format_type(oid: int, typemod: int) -> text'
    description: Returns the canonical SQL name for the type specified by `oid` with `typemod` applied.
  - signature: 'current_schema() -> text'
    description: |
      Returns the name of the first non-implicit schema on the search path, or
      `NULL` if the search path is empty.
    unmaterializable: true
  - signature: 'current_schemas(include_implicit: bool) -> text[]'
    description: |
      Returns the names of the schemas on the search path.
      The `include_implicit` parameter controls whether implicit schemas like
      `mz_catalog` and `pg_catalog` are included in the output.
    unmaterializable: true
  - signature: 'current_setting(setting_name: text[, missing_ok: bool]) -> text'
    description: |
      Returns the value of the named setting or error if it does not exist.
      If `missing_ok` is true, return NULL if it does not exist.
    unmaterializable: true
  - signature: 'obj_description(oid: oid, catalog: text) -> text'
    description: Returns the comment for a database object specified by its `oid` and the
      name of the containing system catalog.
  - signature: 'col_description(oid: oid, column: int) -> text'
    description: Returns the comment for a table column, which is specified by the `oid` of
      its table and its column number.
  - signature: 'pg_backend_pid() -> int'
    description: Returns the internal connection ID.
    unmaterializable: true
  - signature: 'pg_cancel_backend(connection_id: int) -> bool'
    description: |
      Cancels an in-progress query on the specified connection ID.
      Returns whether the connection ID existed (not if it cancelled a query).
    side_effecting: true
  - signature: 'pg_column_size(expr: any) -> int'
    description: Returns the number of bytes used to store any individual data value.
  - signature: 'pg_size_pretty(expr: numeric) -> text'
    description: Converts a size in bytes into a human-readable format.
  - signature: 'pg_get_constraintdef(oid: oid[, pretty: bool]) -> text'
    description: |
      Returns the constraint definition for the given `oid`. Currently always
      returns NULL since constraints aren't supported.
  - signature: 'pg_get_indexdef(index: oid[, column: integer, pretty: bool]) -> text'
    description: |
      Reconstructs the creating command for an index. (This is a decompiled
      reconstruction, not the original text of the command.) If column is
      supplied and is not zero, only the definition of that column is reconstructed.
  - signature: 'pg_get_ruledef(rule_oid: oid[, pretty bool]) -> text'
    description: |
      Reconstructs the creating command for a rule. This function
      always returns NULL because Materialize does not support rules.
  - signature: 'pg_get_userbyid(role: oid) -> text'
    description: |
      Returns the role (user) name for the given `oid`. If no role matches the
      specified OID, the string `unknown (OID=oid)` is returned.
  - signature: 'pg_get_viewdef(view_name: text[, pretty: bool]) -> text'
    description: Returns the underlying SELECT command for the given view.
  - signature: 'pg_get_viewdef(view_oid: oid[, pretty: bool]) -> text'
    description: Returns the underlying SELECT command for the given view.
  - signature: 'pg_get_viewdef(view_oid: oid[, wrap_column: integer]) -> text'
    description: Returns the underlying SELECT command for the given view.
  - signature: 'pg_has_role([user: name or oid,] role: text or oid, privilege: text) -> bool'
    description: Alias for `has_role` for PostgreSQL compatibility.
  - signature: 'pg_is_in_recovery() -> bool'
    description: Returns if the a recovery is still in progress.
  - signature: 'pg_table_is_visible(relation: oid) -> boolean'
    description: Reports whether the relation with the specified OID is visible in the search path.
  - signature: 'pg_tablespace_location(tablespace: oid) -> text'
    description: Returns the path in the file system that the provided tablespace is on.
  - signature: 'pg_type_is_visible(relation: oid) -> boolean'
    description: Reports whether the type with the specified OID is visible in the search path.
  - signature: 'pg_function_is_visible(relation: oid) -> boolean'
    description: Reports whether the function with the specified OID is visible in the search path.
  - signature: 'pg_typeof(expr: any) -> text'
    description: Returns the type of its input argument as a string.
  - signature: 'pg_encoding_to_char(encoding_id: integer) -> text'
    description: PostgreSQL compatibility shim. Not intended for direct use.
  - signature: 'pg_postmaster_start_time() -> timestamptz'
    description: Returns the time when the server started.
    unmaterializable: true
  - signature: 'pg_relation_size(relation: regclass[, fork: text]) -> bigint'
    description: |
      Disk space used by the specified fork ('main', 'fsm', 'vm', or 'init')
      of the specified table or index. If no fork is specified, it defaults
      to 'main'. This function always returns -1 because Materialize does
      not store tables and indexes on local disk.
  - signature: 'pg_stat_get_numscans(oid: oid) -> bigint'
    description: |
       Number of sequential scans done when argument is a table,
       or number of index scans done when argument is an index.
       This function always returns -1 because Materialize does
       not collect statistics.
  - signature: 'version() -> text'
    description: Returns a PostgreSQL-compatible version string.
    unmaterializable: true

- type: Access privilege inquiry
  description: |
    Functions that allow querying object access privileges. None of the following functions consider
    whether the provided role is a _superuser_ or not.
  functions:
    - signature: 'has_cluster_privilege([role: text or oid,] cluster: text, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the cluster with the specified cluster name. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_connection_privilege([role: text or oid,] connection: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the connection with the specified connection name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_database_privilege([role: text or oid,] database: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the database with the specified database name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_schema_privilege([role: text or oid,] schema: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the schema with the specified schema name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_role([user: name or oid,] role: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the `user` has the privilege for `role`. `privilege` can either be `MEMBER`
        or `USAGE`, however currently this value is ignored. The `PUBLIC` pseudo-role cannot be used
        for the `user` nor the `role`. If the `user` is omitted then the `current_role` is assumed.
    - signature: 'has_secret_privilege([role: text or oid,] secret: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the secret with the specified secret name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_system_privilege([role: text or oid,] privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the system privilege.
        If the role is omitted then the `current_role` is assumed.
    - signature: 'has_table_privilege([role: text or oid,] relation: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the relation with the specified relation name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'has_type_privilege([role: text or oid,] type: text or oid, privilege: text) -> bool'
      description: |
        Reports whether the role with the specified role name or OID has the privilege on
        the type with the specified type name or OID. If the role is omitted then
        the `current_role` is assumed.
    - signature: 'mz_is_superuser() -> bool'
      description: Reports whether the `current_role` is a superuser.