Associative array constructors

Updated at:

PolarDB for PostgreSQL (Compatible with Oracle) supports associative array constructors — a concise syntax that lets you create and batch-initialize an associative array in a single statement. Without constructors, you must assign values to each key individually:

-- Traditional approach: one assignment per key
aarray('first') := 10;
aarray('second') := 20;
aarray('third') := 30;

With a constructor, the same initialization takes one line:

aarray := aarray_type('first' => 10, 'second' => 20, 'third' => 30);

Prerequisites

Before you begin, make sure that your PolarDB for PostgreSQL (Compatible with Oracle) cluster meets the following requirement:

  • Minor engine version: 2.0.14.17.36.0 or later

To check your minor engine version, run SHOW polardb_version; or check the console. If your cluster does not meet this requirement, upgrade the minor engine version.

How it works

A constructor is a function with the same name as the associative array type. Pass one or more key-value pairs in key => value format to initialize the array. Use the constructor:

  • In the DECLARE section to initialize at declaration time

  • In the BEGIN...END section to assign or reassign values

  • As a function return value

Key constraints:

  • The constructor name must match the associative array type name exactly.

  • Each key must match the index type declared in the INDEX BY clause.

  • Each value must match the element type declared in the TABLE OF clause.

Syntax

array_variable := array_type(key1 => value1, key2 => value2, ...);

Parameters

ParameterDescription
array_variableThe declared associative array variable
array_typeThe type name of the associative array
keyMust match the index type in the INDEX BY clause
valueMust match the element type in the TABLE OF clause

Examples

Example 1: Initialize an array in a PL/SQL block

Define a local associative array type in DECLARE, then initialize it with a constructor in the execution section.

DECLARE
    -- Declare a local associative array type with a VARCHAR key and an INT value.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);

    -- Declare a variable of this type.
    aarray aarray_type;
BEGIN
    -- Initialize the array using a constructor.
    aarray := aarray_type('first' => 10, 'second' => 20, 'third' => 30);

    -- Print the array content and the number of elements.
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'Number of elements: %', aarray.COUNT;
END;
/

Expected output:

NOTICE: Array content: (first=>10,second=>20,third=>30)
NOTICE: Number of elements: 3

Example 2: Use a constructor in a package

Define a global associative array type in a package, then use a constructor to initialize an array as an OUT parameter.

-- 1. Create the package specification.
CREATE OR REPLACE PACKAGE test_package AS
    -- Declare a global associative array type.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);

    -- Declare a procedure that uses a constructor to initialize an array.
    PROCEDURE init_array(aarray OUT aarray_type);
END;
/

-- 2. Create the package body.
CREATE OR REPLACE PACKAGE BODY test_package AS
    PROCEDURE init_array(aarray OUT aarray_type) IS
    BEGIN
        aarray := aarray_type('one' => 1, 'two' => 2, 'three' => 3);
    END;
END;
/

-- 3. Call the procedure in the package.
DECLARE
    my_array test_package.aarray_type;
BEGIN
    test_package.init_array(my_array);
    RAISE NOTICE 'Array content: %', my_array;
    RAISE NOTICE 'Value of key "two": %', my_array('two');
END;
/

Expected output:

NOTICE: Array content: (one=>1,three=>3,two=>2)
NOTICE: Value of key "two": 2
Keys are returned in sorted order (alphabetical for string keys), not in insertion order.

Example 3: Use a constructor as a function return value

A function can return an associative array created directly by a constructor.

DECLARE
    -- Declare the associative array type.
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);

    -- Declare a function that returns an associative array.
    FUNCTION create_array RETURN aarray_type IS
    BEGIN
        -- Directly use the constructor as the return value.
        RETURN aarray_type('alpha' => 100, 'beta' => 200, 'gamma' => 300);
    END;

    result_array aarray_type;
BEGIN
    result_array := create_array();
    RAISE NOTICE 'Returned array: %', result_array;
    RAISE NOTICE 'Value of key "gamma": %', result_array('gamma');
END;
/

Expected output:

NOTICE: Returned array: (alpha=>100,beta=>200,gamma=>300)
NOTICE: Value of key "gamma": 300

Example 4: Initialize a multidimensional array

Constructors support nested usage to initialize multi-dimensional associative arrays.

DECLARE
    -- Define inner and outer associative array types.
    TYPE aarray_type1 IS TABLE OF INT INDEX BY VARCHAR(10);
    TYPE aarray_type2 IS TABLE OF aarray_type1 INDEX BY VARCHAR(10);

    aarray aarray_type2;
BEGIN
    -- Use a nested constructor to initialize the two-dimensional array.
    aarray := aarray_type2(
        'row1' => aarray_type1('col1' => 1, 'col2' => 2),
        'row2' => aarray_type1('col1' => 3, 'col2' => 4)
    );

    RAISE NOTICE 'Complete array: %', aarray;
    RAISE NOTICE 'Value of element [row2][col1]: %', aarray('row2')('col1');
END;
/

Expected output:

NOTICE: Complete array: (row1=>"(col1=>1,col2=>2)",row2=>"(col1=>3,col2=>4)")
NOTICE: Value of element [row2][col1]: 3

Behavior notes

Empty constructor

Pass no arguments to create an array with zero elements. Add elements afterward using standard key assignment.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type(); -- Use an empty constructor.
    RAISE NOTICE 'Number of elements in the empty array: %', aarray.COUNT;

    -- Add elements later.
    aarray('new_key') := 42;
    RAISE NOTICE 'After adding an element: %', aarray;
END;
/

Expected output:

NOTICE: Number of elements in the empty array: 0
NOTICE: After adding an element: (new_key=>42)

Duplicate keys

If the constructor contains duplicate keys, the later value overwrites the earlier one.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type('key1' => 10, 'key2' => 20, 'key1' => 30);
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'Final value of key1: %', aarray('key1');
END;
/

Expected output:

NOTICE: Array content: (key1=>30,key2=>20)
NOTICE: Final value of key1: 30

Key order

The order of key-value pairs in the constructor does not affect how elements are stored. Keys are sorted internally based on their data type — alphabetically for string keys, numerically for integer keys.

DECLARE
    TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
    aarray aarray_type;
BEGIN
    aarray := aarray_type('zebra' => 1, 'apple' => 2, 'banana' => 3);
    -- Although 'zebra' is first, the keys are stored in alphabetical order.
    RAISE NOTICE 'Array content: %', aarray;
    RAISE NOTICE 'First key: %', aarray.FIRST;
    RAISE NOTICE 'Last key: %', aarray.LAST;
END;
/

Expected output:

NOTICE: Array content: (apple=>2,banana=>3,zebra=>1)
NOTICE: First key: apple
NOTICE: Last key: zebra