Nested Functions

This section describes functions and operators for examining and manipulating nested values. There are two nested data types: lists and structs.

List Functions

In the descriptions, l is the three element list [4, 5, 6].

Function Description Example Result
array_extract(list, index) Alias for list_extract. array_extract(l, 2) 6
array_slice(list, begin, end) Extract a sublist using slice conventions. NULLs are interpreted as the bounds of the LIST. Negative values are accepted. array_slice(l, 1, NULL) [5,6]
list_element(list, index) Alias for list_extract. list_element(l, 2) 6
list_extract(list, index) Extract the indexth (0-based) value from the list. list_extract(l, 2) 6
list_pack(any, ...) Alias for list_value. list_pack(4, 5, 6) [4, 5, 6]
list_value(any, ...) Create a LIST containing the argument values. list_value(4, 5, 6) [4, 5, 6]
list[index] Alias for list_extract. l[2] 6
list[begin:end] Alias for array_slice. Missing arguments are interprete as NULLs l[1:2] [5, 6]
array_length(list) Return the length of the list array_length([1, 2, 3]) 3
len(list) Alias for array_length len([1, 2, 3]) 3

Struct Functions

Function Description Example Result
struct[entry] Alias for struct_extract. struct_extract(s, 'i') 4
row(any, ...) Create a STRUCT containing the argument values. If the values are column references, the entry name will be the column name; otherwise it will be the string 'vN' where N is the (1-based) position of the argument. row(i, i % 4, i / 4) {'i': 3, 'v2': 3, 'v3': 0}
struct_extract(struct, 'entry') Extract the named entry from the struct. struct_extract(s, 'i') 4
struct_pack(name := any, ...) Create a STRUCT containing the argument values. The entry name will be the bound variable name. struct_pack(i := 4, s := 'string') {'i': 3, 's': 'string'}

Range Functions

The functions range and generate_series create a list of values in the range between start and stop. The start parameter is inclusive. For the range function, the stop parameter is exclusive, while for generate_series, it is inclusive.

Based on the number of arguments, the following variants exist:

  • range(start, stop, step)
  • range(start, stop)
  • range(stop)
  • generate_series(start, stop, step)
  • generate_series(start, stop)
  • generate_series(stop)

The default value of start is 0 and the default value of step is 1.

SELECT range(5);
-- [0, 1, 2, 3, 4]

SELECT range(2, 5);
-- [2, 3, 4]

SELECT range(2, 5, 3);
-- [2]

SELECT generate_series(5);
-- [0, 1, 2, 3, 4, 5]

SELECT generate_series(2, 5);
-- [2, 3, 4, 5]

SELECT generate_series(2, 5, 3);
-- [2, 5]

Date ranges are also supported:

SELECT * FROM range(date '1992-01-01', date '1992-03-01', interval '1' month);

┌─────────────────────┐
│        range        │
├─────────────────────┤
│ 1992-01-01 00:00:00 │
│ 1992-02-01 00:00:00 │
└─────────────────────┘

There are also aggregate functions list and histogram that produces lists and lists of structs.