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[()]
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[()]
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)
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[()]
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[()]
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)
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)
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)
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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot