Collection type conversion - PolarDB - Alibaba Cloud Documentation Center

In PolarDB for PostgreSQL (Compatible with Oracle), you can use MULTISET CAST to convert query results into specified collection types (such as nested tables). This feature allows query results to be flexibly encapsulated as collection structures, enabling collection-related operations in PL/SQL programs.

Characteristics and scenarios

Flexible collection construction: MULTISET CAST provides a convenient way to dynamically encapsulate query results into operable collection formats.
Complex operations: It can be widely applied to advanced features in PL/SQL such as collection filtering, intersection, difference, and other operations.
Clear type conversion: Supports explicit conversion of query results to target collection types, making collection operations and variable assignments easier.

Supported versions

This feature is supported on clusters that run PolarDB for PostgreSQL (Compatible with Oracle) 2.0 with revision version 2.0.14.17.33.0 or later.

Note

You can view the minor engine version in the console, or check it by using the SHOW polardb_version; statement. If the minor engine version requirement is not met, upgrade the minor engine version.

Syntax

CAST(MULTISET(<subquery>) AS <collection_type>)

Parameters

Parameter

Description

<subquery>

The subquery statement to be converted to a collection type.

Its return value must be compatible with the target collection type.

<collection_type>

The type of the collection. This must be a valid collection type such as the nested table type TABLE OF.

Use MULTISET to represent query results as a collection, then use CAST to convert it to the required collection type.

Note

The MULTISET CAST operation has collection constraints. Please ensure that the target collection type matches the data type of the query result columns.

Examples

The following example demonstrates how to use MULTISET CAST to convert query results into a nested table type and perform operations on the results.

CREATE OR REPLACE TYPE num_table AS TABLE OF NUMBER;

DECLARE
    nt1 num_table := num_table(1, 2, 3, 4, 5); -- First nested table
    nt2 num_table := num_table(4, 5, 6, 7, 8); -- Second nested table
    intersection_result num_table; -- Nested table for storing intersection results
BEGIN
    -- Use MULTISET CAST to get query results as a nested table
    SELECT CAST(MULTISET(
        SELECT * FROM TABLE(nt1)
        INTERSECT
        SELECT * FROM TABLE(nt2)
    ) AS num_table)
    INTO intersection_result
    FROM dual;

    -- Output collection results
    DBMS_OUTPUT.PUT_LINE('Intersection of nt1 and nt2:');
    FOR i IN 1 .. intersection_result.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(intersection_result(i));
    END LOOP;
END;

For the nested table num_table and the collection operation INTERSECT, the results are finally stored as an intersected nested table type using MULTISET CAST. Sample results:

Intersection of nt1 and nt2:
5
4