Associative array constructors
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
DECLAREsection to initialize at declaration timeIn the
BEGIN...ENDsection to assign or reassign valuesAs 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 BYclause.Each value must match the element type declared in the
TABLE OFclause.
Syntax
array_variable := array_type(key1 => value1, key2 => value2, ...);Parameters
| Parameter | Description |
|---|---|
array_variable | The declared associative array variable |
array_type | The type name of the associative array |
key | Must match the index type in the INDEX BY clause |
value | Must 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: 3Example 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": 2Keys 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": 300Example 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]: 3Behavior 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: 30Key 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