Collection operations - PolarDB - Alibaba Cloud Documentation Center

When you work with PL/SQL collection types, such as associative arrays and nested tables, you often need to query elements, manage data, or perform comparisons and operations between collections. To simplify these tasks, PolarDB for PostgreSQL (Compatible with Oracle) provides a rich set of built-in collection methods and operators. These tools allow you to manage collection data intuitively and efficiently, much like standard data structures, thereby enhancing code readability and maintainability.

Overview

Collection operations are a set of built-in methods and operators for PL/SQL collection types, such as associative arrays and nested tables. They provide features for querying, modifying, managing, and comparing collections using a simple and intuitive syntax. You can use them to compare or operate on two compatible collections. For example, you can check for a subset relationship with SUBMULTISET or calculate a union with MULTISET UNION.

collection_variable.method_name[(parameters)]

Applicability

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster must run minor engine version 2.0.14.17.36.0 or later.

Note

You can view the minor engine version in the console or run the SHOW polardb_version; statement. If your cluster does not meet this requirement, you can upgrade the minor engine version.

Operations

Multiset operators

IS_EMPTY

Checks if a collection is empty.

collection IS EMPTY

Return value

A BOOLEAN value.

TRUE: if the collection is empty or not initialized.
FALSE: if the collection contains at least one element.

Features

Functionally equivalent to collection.COUNT = 0, but offers better performance.
Returns TRUE for a NULL collection.

IS_NOT_EMPTY

Checks if a collection is not empty.

collection IS NOT EMPTY

Return value

A BOOLEAN value.

TRUE: if the collection contains at least one element.
FALSE: if the collection is empty or not initialized.

Features

Equivalent to collection.COUNT > 0.
The logical opposite of IS_EMPTY.

IS_IN

Checks if a specified value exists in a collection.

element_value IN (collection)

Parameters

element_value: The value to search for.
Return value

A BOOLEAN value.

TRUE: if the value exists in the collection.
FALSE: if the value does not exist in the collection.

Features

Supports NULL value comparison.
Especially useful for nested tables and VARRAYs.
The time complexity is O(n).

IS_NOT_IN

Checks if a specified value does not exist in a collection.

element_value NOT IN (collection)

Parameters

element_value: The value to search for.

Return value

A BOOLEAN value.

TRUE: if the value does not exist in the collection.
FALSE: if the value exists in the collection.

Features

The logical opposite of the IN operator.
Requires special attention when handling NULL values.

IS_SUBMULTISET

Checks if the first collection is a submultiset of the second collection.

collection1 SUBMULTISET collection2

Return value

A BOOLEAN value.

TRUE: if collection1 is a subset of collection2.
FALSE: if collection1 is not a subset of collection2.

Features

The check considers the number of duplicate elements. For example, {1} is a subset of {1, 2}, but {1, 1} is not.
An empty collection is a subset of any collection.
Any collection is a subset of itself.

IS_NOT_SUBMULTISET

Checks if the first collection is not a submultiset of the second collection.

collection1 NOT SUBMULTISET collection2

Return value

A BOOLEAN value.

TRUE: if collection1 is not a subset of collection2.
FALSE: if collection1 is a subset of collection2.

IS_MEMBER

Checks if a specified element exists in a collection.

element_value IN (collection)

Parameters

element_value: The element value to check.

Return value

A BOOLEAN value.

TRUE: if the element is a member of the collection.
FALSE: if the element is not a member of the collection.

Features

Functionally similar to the IS_IN operator, but with a different syntax.
Supports comparison of complex data types, such as the RECORD type.

IS_NOT_MEMBER

Checks if a specified element does not exist in a collection.

element_value NOT MEMBER OF collection

Parameters

element_value: The element value to check.

Return value

A BOOLEAN value.

TRUE: if the element is not a member of the collection.
FALSE: if the element is a member of the collection.

IS_SET

Checks if a collection contains no duplicate elements, which means it qualifies as a set in the mathematical sense.

collection IS A SET

Return value

A BOOLEAN value.

TRUE: if the collection has no duplicate elements.
FALSE: if the collection has duplicate elements.

Features

For associative arrays, this method always returns TRUE because their keys are unique.
For nested tables, it checks the uniqueness of values.

IS_NOT_SET

Checks if a collection contains duplicate elements.

collection IS NOT A SET

Return value

A BOOLEAN value.

TRUE: if the collection has duplicate elements.
FALSE: if the collection has no duplicate elements.

Multiset operators

MULTISET UNION

Calculates the union of two collections and returns a new collection.

collection1 MULTISET UNION [ALL | DISTINCT] collection2

Variants

MULTISET UNION: This is the default behavior. The result includes all elements from both collection1 and collection2. If an element exists in both collections, it appears twice in the result.
MULTISET UNION ALL: Equivalent to MULTISET UNION.
MULTISET UNION DISTINCT: The result includes all elements from collection1 and collection2, but duplicates are removed. Each element appears only once.

Return value

A new collection that contains all elements from both collections. The element type is the same as the input collections.

MULTISET INTERSECT

Returns the intersection of two collections as a new collection.

collection1 MULTISET INTERSECT [ALL | DISTINCT] collection2

Variants

MULTISET INTERSECT: This is the default behavior. It returns elements that are common to both collections. If an element appears n times in collection1 and m times in collection2, it appears MIN(n, m) times in the result.
MULTISET INTERSECT ALL: Equivalent to MULTISET INTERSECT.
MULTISET INTERSECT DISTINCT: Returns the common elements from both collections, with duplicates removed. Each element appears only once in the result.

Return value

A new collection containing the elements common to both collections.

MULTISET EXCEPT

Calculates the difference between two collections. It returns a new collection that contains elements from collection1 that are not in collection2.

collection1 MULTISET EXCEPT [ALL | DISTINCT] collection2

Variants

MULTISET EXCEPT: This is the default behavior. It removes elements from collection1 that also exist in collection2. If an element appears n times in collection1 and m times in collection2, it appears MAX(n - m, 0) times in the result.
MULTISET EXCEPT ALL: Equivalent to MULTISET EXCEPT.
MULTISET EXCEPT DISTINCT: Removes duplicates from both collection1 and collection2. This operation then returns elements that are in the deduplicated collection1 but not in the deduplicated collection2.

Return value

A new collection that contains elements from the first collection that are not in the second collection.

Examples

The following examples show how to use collection methods and operators.

Example 1: Manage an associative array using collection methods

This example shows how to use methods such as COUNT, FIRST, LAST, EXISTS, and DELETE to manage an associative array.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    -- Initialize the array
    aarray('apple')  := 10;
    aarray('banana') := 20;
    aarray('cherry') := 30;

    -- 1. Query the collection status
    RAISE NOTICE 'Number of elements (COUNT): %', aarray.COUNT;
    RAISE NOTICE 'First key (FIRST): %', aarray.FIRST;
    RAISE NOTICE 'Last key (LAST): %', aarray.LAST;

    -- 2. Check if an element exists
    RAISE NOTICE 'Does key "banana" exist (EXISTS): %', aarray.EXISTS('banana');
    RAISE NOTICE 'Does key "date" exist (EXISTS): %', aarray.EXISTS('date');
    
    -- 3. Delete an element
    RAISE NOTICE 'Before deletion: %', aarray;
    aarray.DELETE('banana');
    RAISE NOTICE 'After deleting "banana": %', aarray;

    -- 4. Delete all elements from the collection
    aarray.DELETE;
    RAISE NOTICE 'Number of elements after deleting all: %', aarray.COUNT;
END;
/

Expected output:

NOTICE: Number of elements (COUNT): 3
NOTICE: First key (FIRST): apple
NOTICE: Last key (LAST): cherry
NOTICE: Does key "banana" exist (EXISTS): t
NOTICE: Does key "date" exist (EXISTS): f
NOTICE: Before deletion: (apple=>10,banana=>20,cherry=>30)
NOTICE: After deleting "banana": (apple=>10,cherry=>30)
NOTICE: Number of elements after deleting all: 0

Example 2: Traverse a collection using `NEXT` and `PRIOR`

This example shows how to use FIRST and NEXT together to safely traverse all elements of an associative array.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
    v_key VARCHAR(10);
BEGIN
    aarray('alpha') := 1;
    aarray('beta')  := 2;
    aarray('gamma') := 3;

    -- Traverse using a loop
    v_key := aarray.FIRST;
    WHILE v_key IS NOT NULL LOOP
        RAISE NOTICE 'Key: %, Value: %', v_key, aarray(v_key);
        v_key := aarray.NEXT(v_key);
    END LOOP;
    
    -- Query the previous key
    RAISE NOTICE 'Previous key of "gamma" (PRIOR): %', aarray.PRIOR('gamma');
END;
/

Expected output (Note: Associative arrays are internally sorted by key):

NOTICE: Key: alpha, Value: 1
NOTICE: Key: beta, Value: 2
NOTICE: Key: gamma, Value: 3
NOTICE: Previous key of "gamma" (PRIOR): beta

Example 3: Use multiset operators

This example shows how to perform set operations on two nested tables using MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT.

DECLARE
    TYPE ntable_type IS TABLE OF INT;
    ntable1 ntable_type := ntable_type(1, 2, 2, 3);
    ntable2 ntable_type := ntable_type(2, 3, 4, 4);
    result_table ntable_type;
BEGIN
    -- 1. Union
    result_table := ntable1 MULTISET UNION ntable2;
    RAISE NOTICE 'Union (UNION): %', result_table;

    -- 2. Intersection
    result_table := ntable1 MULTISET INTERSECT ntable2;
    RAISE NOTICE 'Intersection (INTERSECT): %', result_table;

    -- 3. Difference
    result_table := ntable1 MULTISET EXCEPT ntable2;
    RAISE NOTICE 'Difference (EXCEPT): %', result_table;
END;
/

Expected output:

NOTICE: Union (UNION): {1,2,2,3,2,3,4,4}
NOTICE: Intersection (INTERSECT): {2,3}
NOTICE: Difference (EXCEPT): {1,2}

Example 4: Manage a nested table using `EXTEND`

This example shows how to use the EXTEND method to add new elements to a nested table.

DECLARE
    TYPE ntable_type IS TABLE OF INT;
    ntable ntable_type := ntable_type(10, 20);
BEGIN
    RAISE NOTICE 'Original nested table: %', ntable;

    -- Extend with 2 NULL elements
    ntable.EXTEND(2); 
    RAISE NOTICE 'After extending with 2 elements: %', ntable;

    -- Assign values to the new elements
    ntable(3) := 30;
    ntable(4) := 40;
    RAISE NOTICE 'After assignment: %', ntable;
END;
/

Expected output:

NOTICE: Original nested table: {10,20}
NOTICE: After extending with 2 elements: {10,20,NULL,NULL}
NOTICE: After assignment: {10,20,30,40}

Overview

Applicability

Operations

Multiset operators

IS_EMPTY

IS_NOT_EMPTY

IS_IN

IS_NOT_IN

IS_SUBMULTISET

IS_NOT_SUBMULTISET

IS_MEMBER

IS_NOT_MEMBER

IS_SET

IS_NOT_SET

Multiset operators

MULTISET UNION

MULTISET INTERSECT

MULTISET EXCEPT

Examples

Example 1: Manage an associative array using collection methods

Example 2: Traverse a collection using NEXT and PRIOR

Example 3: Use multiset operators

Example 4: Manage a nested table using EXTEND

Example 2: Traverse a collection using `NEXT` and `PRIOR`

Example 4: Manage a nested table using `EXTEND`