Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Stored Procedures/ Arrays, Collections, and Records/ Arrays/ Functions Supported by Arrays
Updated on 2025-09-22 GMT+08:00
View PDF
Share

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
Parent topic: Arrays