Updated on 2025-05-29 GMT+08:00

Functions Supported by Arrays

  • The following cases are used in ORA-compatible mode. In the function definition description, the content in [] is optional. For example, count[()] can be written as count or count().
  • Currently, the following errors are not supported: array uninitialized error (Reference to uninitialized collection), array index out-of-bounds error (Subscript beyond count), and array index out-of-limit error (Subscript outside of limit).
  • In an inner expression, functions of the array type cannot be called in nesting mode.
  • extend[(count)]

    Parameter: count is of the int4 type.

    Return value: No value is returned.

    Description: Extends one or count elements at the end of a varray variable. The default value is NULL.

    Example 1: The extend is used to extend one element. The default value is NULL.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer();
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.extend;
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$# end;
    gaussdb$# /
    0
    1
    ANONYMOUS BLOCK EXECUTE

    Example 2: The extend(count) is used to extend the count elements. The default value is NULL.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer();
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.extend(3);
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$# end;
    gaussdb$# /
    0
    3
    ANONYMOUS BLOCK EXECUTE
  • extend(count, idx)

    Parameters: idx and count are of the INT4 type.

    Return value: No value is returned.

    Description: Extends count elements at the end of a varray variable, and the value of the extended element is equal to the value of the given idx index element.

    Example: The extend(count, idx) is used to extend the element at the idx position.

    gaussdb=# set a_format_version='10c';
    SET
    gaussdb=# set a_format_dev_version='s1';
    SET
    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1,2);
    gaussdb-# begin
    gaussdb$#     arrint.extend(2, 1);
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     for i in 1..arrint.count loop
    gaussdb$#         dbe_output.print_line(arrint(i));
    gaussdb$#     end loop;
    gaussdb$# end;
    gaussdb$# /
    4
    1
    2
    1
    1
    ANONYMOUS BLOCK EXECUTE
  • count[()]

    Parameter: none.

    Return value: INT4 type.

    Description: Returns the number of elements in an array.

    Example: View the number of initialized array elements.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer;
    gaussdb-#     len int;
    gaussdb-# begin
    gaussdb$#     arrint := array_integer(1, 2, 3);
    gaussdb$#     len := arrint.count();
    gaussdb$#     dbe_output.print_line(len);
    gaussdb$# end;
    gaussdb$# /
    3
    ANONYMOUS BLOCK EXECUTE
  • trim[(n)]

    Parameter: n is of the INT4 type.

    Return value: No value is returned.

    Description: Deletes an element at the end of the array if there is no parameter. Alternatively, deletes a specified number of elements at the end of the array if the input parameter n is specified.

    Example: Delete n elements from an array.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1,2,3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.trim(1);
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$# end;
    gaussdb$# /
    3
    2
    ANONYMOUS BLOCK EXECUTE
    -- If n is greater than the number of array elements, clear all array elements.
    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1,2,3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.trim(4);
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$# end;
    gaussdb$# /
    3
    0
    ANONYMOUS BLOCK EXECUTE
  • delete[()]

    Parameter: none

    Return value: No value is returned.

    Description: Clears elements in an array.

    Example: Clear elements in an array.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1,2,3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.delete();
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$# end;
    gaussdb$# /
    3
    0
    ANONYMOUS BLOCK EXECUTE
    -- The array is not initialized.
    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer;
    gaussdb-# begin
    gaussdb$#     arrint.delete();
    gaussdb$# end;
    gaussdb$# /
    ANONYMOUS BLOCK EXECUTE
  • delete(idx)

    Parameter: INT4 type.

    Return value: No value is returned.

    Description: If the specified index idx is within the array range, the element with the specified index idx is deleted from the array.

    Example 1: The given index idx is within the array range.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1, 2, 3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.delete(2);
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     for i in 1..arrint.count loop
    gaussdb$#         dbe_output.print_line(arrint(i));
    gaussdb$#     end loop;
    gaussdb$# end;
    gaussdb$# /
    3
    2
    1
    3
    ANONYMOUS BLOCK EXECUTE

    Example 2: The given index idx is out of the array range.

    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer := array_integer(1, 2, 3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.delete(4);
    gaussdb$#     raise info '%', arrint;
    gaussdb$# end;
    gaussdb$# /
    3
    INFO:  {1,2,3}
    ANONYMOUS BLOCK EXECUTE
    gaussdb=# declare
    gaussdb-#     type array_integer is varray(10) of integer;
    gaussdb-#     arrint array_integer:= array_integer(1, 2, 3);
    gaussdb-# begin
    gaussdb$#     dbe_output.print_line(arrint.count);
    gaussdb$#     arrint.delete(11);
    gaussdb$#     raise info '%', arrint;
    gaussdb$# end;
    gaussdb$# /
    3
    INFO:  {1,2,3}
    ANONYMOUS BLOCK EXECUTE
  • first[()]

    Parameter: none.

    Return value: INT4 type.

    Description: Returns the index of the first element in an array. If there is no first element, NULL is returned.

    Example:

    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc');
    gaussdb-# begin
    gaussdb$#     raise info 'first is %',v.first();
    gaussdb$# end;
    gaussdb$# /
    INFO:  first is 1
    ANONYMOUS BLOCK EXECUTE
    -- If the array is not initialized, NULL is returned.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr;
    gaussdb-# begin
    gaussdb$#     raise info 'first is %',v.first;
    gaussdb$# end;
    gaussdb$# /
    INFO:  first is <NULL>
    ANONYMOUS BLOCK EXECUTE
  • last[()]

    Parameter: none.

    Return value: INT4 type.

    Description: Returns the index of the last element in an array. If there is no last element, NULL is returned.

    Example:

    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc');
    gaussdb-# begin
    gaussdb$#     raise info 'last is %',v.last();
    gaussdb$# end;
    gaussdb$# /
    INFO:  last is 2
    ANONYMOUS BLOCK EXECUTE
    -- If the array is not initialized, NULL is returned.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr;
    gaussdb-# begin
    gaussdb$#     raise info 'last is %',v.last;
    gaussdb$# end;
    gaussdb$# /
    INFO:  last is <NULL>
    ANONYMOUS BLOCK EXECUTE
  • prior(idx)

    Parameter: int4 type.

    Return value: INT4 type.

    Description: Returns the index of the element before the specified index in an array. If the index of the element cannot be found, NULL is returned.

    Example:

    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc');
    gaussdb-# begin
    gaussdb$#     raise info 'prior is %',v.prior(2);
    gaussdb$# end;
    gaussdb$# /
    INFO:  prior is 1
    ANONYMOUS BLOCK EXECUTE
    -- If the array is not initialized, NULL is returned.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr;
    gaussdb-# begin
    gaussdb$#     raise info 'prior is %',v.prior(2);
    gaussdb$# end;
    gaussdb$# /
    INFO:  prior is <NULL>
    ANONYMOUS BLOCK EXECUTE
    -- The index is out of range and is greater than the array range.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc','qwe');
    gaussdb-# begin
    gaussdb$#     raise info 'prior is %',v.prior(10);
    gaussdb$# end;
    gaussdb$# /
    INFO:  prior is 3
    ANONYMOUS BLOCK EXECUTE
  • next(idx)

    Parameter: int4 type.

    Return value: INT4 type.

    Description: Returns the index of the element following the specified index in an array. If the index of an element cannot be found, NULL is returned.

    Example:

    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc');
    gaussdb-# begin
    gaussdb$#     raise info 'next is %',v.next(1);
    gaussdb$# end;
    gaussdb$# /
    INFO:  next is 2
    ANONYMOUS BLOCK EXECUTE
    -- If the array is not initialized, NULL is returned.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr;
    gaussdb-# begin
    gaussdb$#     raise info 'next  is %',v.next(1);
    gaussdb$# end;
    gaussdb$# /
    INFO:  next  is <NULL>
    ANONYMOUS BLOCK EXECUTE
    -- The index is out of range and is greater than the array range.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc','qwe');
    gaussdb-# begin
    gaussdb$#     raise info 'next  is %',v.next(10);
    gaussdb$# end;
    gaussdb$# /
    INFO:  next  is <NULL>
    ANONYMOUS BLOCK EXECUTE
  • exists(idx)

    Parameter: int4 type.

    Return value: TRUE or FALSE, of the Boolean type.

    Function: Checks whether an element exists in a specified position.

    Example:

    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr := varr('asd','zxc');
    gaussdb-#     flag bool;
    gaussdb-# begin
    gaussdb$#     flag := v.exists(1);
    gaussdb$#     raise info '%',flag;
    gaussdb$#     flag := v.exists(3);
    gaussdb$#     raise info '%',flag;
    gaussdb$#     flag := v.exists(7);
    gaussdb$#     raise info '%',flag;
    gaussdb$# end;
    gaussdb$# /
    INFO:  t
    INFO:  f
    INFO:  f
    ANONYMOUS BLOCK EXECUTE
    -- If the array is not initialized, false is returned.
    gaussdb=# declare
    gaussdb-#     type varr is varray(10) of varchar(3);
    gaussdb-#     v varr;
    gaussdb-#     flag bool;
    gaussdb-# begin
    gaussdb$#     flag := v.exists(1);
    gaussdb$#     raise info '%',flag;
    gaussdb$#     flag := v.exists(3);
    gaussdb$#     raise info '%',flag;
    gaussdb$#     flag := v.exists(7);
    gaussdb$#     raise info '%',flag;
    gaussdb$# end;
    gaussdb$# /
    INFO:  f
    INFO:  f
    INFO:  f
    ANONYMOUS BLOCK EXECUTE