Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ Stored Procedure/ Arrays, Collections, and Records/ Arrays/ Functions Supported by Arrays

Updated on 2024-05-07 GMT+08:00

View PDF

Functions Supported by Arrays

In an inner expression, functions of the array type cannot be called in nesting mode.

extend[(count[, idx])]

Parameters: idx and count are of the int4 type.

Return value: No value is returned.

Description: Extends one or count elements at the end of a varray variable. If the index element idx exists, count index elements are copied to the end of the variable.

Example 1: extend(count). 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

Example 2: The value of extend exceeds the defined array size, and the varray_compat parameter is enabled.

gaussdb=# set behavior_compat_options = 'varray_compat';
SET
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$#     arrint.EXTEND(8); -- error
gaussdb$# END;
gaussdb$# /
0
3
ERROR:  Subscript outside of limit
CONTEXT:  PL/SQL function inline_code_block line 8 at assignment

Example 3: extend(count, idx). The element at the idx position is extended.

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

Example 4: extend(count, idx). idx is out of range, and the varray_compat parameter is enabled.

gaussdb=# set a_format_version='10c';
SET
gaussdb=# set a_format_dev_version='s1';
SET
gaussdb=# set behavior_compat_options = 'varray_compat';
SET
gaussdb=# DECLARE
gaussdb-#     TYPE ARRAY_INTEGER IS VARRAY(10) OF INTEGER;
gaussdb-#     arrint ARRAY_INTEGER := ARRAY_INTEGER();
gaussdb-# BEGIN
gaussdb$#     arrint.EXTEND(10, 1); -- error index.
gaussdb$# END;
gaussdb$# /
ERROR:  Subscript beyond count
CONTEXT:  PL/SQL function inline_code_block line 5 at assignment

count[()]

Parameter: none

Return value: int4 type

Description: Returns the number of elements in an array.

Example 1: 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

Example 2: View the number of uninitialized array elements and enable the varray_compat parameter.

gaussdb=# set behavior_compat_options = 'varray_compat';
SET
gaussdb=# DECLARE
gaussdb-#     TYPE ARRAY_INTEGER IS VARRAY(10) OF INTEGER;
gaussdb-#     arrint ARRAY_INTEGER;
gaussdb-#     len int;
gaussdb-# BEGIN
gaussdb$#     len := arrint.COUNT;
gaussdb$#     DBE_OUTPUT.PRINT_LINE(len);
gaussdb$# END;
gaussdb$# /
ERROR:  Reference to uninitialized collection
CONTEXT:  PL/SQL function inline_code_block line 6 at assignment

trim[(n)]

Parameter: n is of the int4 type.

Return value: No value is returned.

Description: Deletes an element space at the end of the array if there is no parameter. Alternatively, it deletes a specified number of element spaces at the end of the array if the input parameter is valid.

Example 1: Delete n elements from an array without enabling the varray_compat parameter.

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
-- n > Number of 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$# END;
gaussdb$# /
3
ANONYMOUS BLOCK EXECUTE

Example 2: Delete n elements from an array and enable the varray_compat parameter.

-- n > Number of 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); -- count > 0.
gaussdb$#     arrint.TRIM(4);
gaussdb$# END;
gaussdb$# /
3
ERROR:  Subscript outside of count
ERROR:  Subscript beyond count
CONTEXT:  PL/SQL function inline_code_block line 6 at assignment
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE ARRAY_INTEGER IS VARRAY(10) OF INTEGER;
gaussdb-#     arrint ARRAY_INTEGER;
gaussdb-# BEGIN
gaussdb$#     arrint.TRIM(1);
gaussdb$# END;
gaussdb$# /
ERROR:  Reference to uninitialized collection
CONTEXT:  PL/SQL function inline_code_block line 5 at assignment

delete[()]

Parameter: none

Return value: No value is returned.

Description: Clears elements in an array.

Example 1: Clear the elements in the array without enabling the varray_compat parameter.

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

Example 2: Clear the elements in the array with the varray_compat parameter enabled.

gaussdb=# set behavior_compat_options = 'varray_compat';
SET
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=# set behavior_compat_options = 'varray_compat';
SET
gaussdb=# DECLARE
gaussdb-#     TYPE ARRAY_INTEGER IS VARRAY(10) OF INTEGER;
gaussdb-#     arrint ARRAY_INTEGER;
gaussdb-# BEGIN
gaussdb$#     arrint.DELETE();
gaussdb$# END;
gaussdb$# /
ERROR:  Reference to uninitialized collection
CONTEXT:  PL/SQL function inline_code_block line 5 at assignment

delete(idx)

Parameter: int4 type

Return value: No value is returned.

Description: Deletes an element at a certain position in an array.

Example 1: Delete an element 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.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 idx is invalid.

-- The varray_compat parameter is not enabled.
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

-- The varray_compat parameter is enabled.
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$# END;
gaussdb$# /
3
ERROR:  Subscript beyond count
CONTEXT:  PL/SQL function inline_code_block line 6 at assignment

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$# END;
gaussdb$# /
3
ERROR:  Subscript outside of limit
CONTEXT:  PL/SQL function inline_code_block line 6 at assignment

first

Parameter: none

Return value: int4 type

Description: Returns the index of the first valid element in a collection.

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
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE VARR IS VARRAY(10) OF varchar(3);
gaussdb-#     v VARR := 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 valid element in an array.

Example 1:

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
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE VARR IS VARRAY(10) OF varchar(3);
gaussdb-#     v VARR := 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 a valid element before the current index in a collection.

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
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE VARR IS VARRAY(10) OF varchar(3);
gaussdb-#     v VARR := VARR();
gaussdb-# BEGIN
gaussdb$#     raise info 'prior is %',v.prior(2);
gaussdb$# END;
gaussdb$# /
INFO:  prior is <NULL>
ANONYMOUS BLOCK EXECUTE
-- Out-of-bounds access
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 a valid element following the current index in a collection.

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
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE VARR IS VARRAY(10) OF varchar(3);
gaussdb-#     v VARR := VARR();
gaussdb-# BEGIN
gaussdb$#     raise info 'next  is %',v.next(1);
gaussdb$# END;
gaussdb$# /
INFO:  next  is <NULL>
ANONYMOUS BLOCK EXECUTE
-- Out-of-bounds access
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.

Description: Checks whether a valid 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
-- The array is not initialized.
gaussdb=# DECLARE
gaussdb-#     TYPE VARR IS VARRAY(10) OF varchar(3);
gaussdb-#     v VARR := 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

The following describes the behavior differences of array functions before and after the GUC parameter varray_compat is enabled:

If the count, extend, trim, delete, first, last, next, or prior function is applied to an uninitialized array, the "Reference to uninitialized collection" error is reported after the parameter is enabled. If the parameter is disabled, no error is reported.
For the extend(count) and extend(count, idx) functions, if the array size is exceeded, the "Subscript outside of limit" error is reported after the parameter is enabled. If the parameter is not enabled, no error is reported.
For the extend(count, idx) function, if the index is invalid, the "Subscript outside of limit" or "Subscript outside of count" is reported after the parameter is enabled. If the parameter is disabled, the "wrong number of array subscripts error" is reported.
For the trim(n) function, if n is greater than the number of array elements, the "Subscript outside of count" error is reported after the parameter is enabled. If the parameter is disabled, no error is reported.
For the delete(idx) function, if the subscript idx is invalid, the "Subscript outside of limit" or "Subscript outside of count" error is reported after the parameter is enabled. If the parameter is disabled, the original array is returned.

Parent topic: Arrays

Previous topic: Use of Array Types

Next topic: Collections