Arrays in SQL statements

An array is an ordered set of elements of a single built-in data type. An array can have an associated user-defined array type, or it can be the result of an SQL operation that returns an array value without an associated user-defined array type.

Arrays can be ordinary arrays and associative arrays.

Ordinary arrays have a user-defined upper bound. Elements in the array can be accessed and modified by their index value. Array elements are referenced in SQL statements by using one-based indexing; for example, MYARRAY[1], MYARRAY[2], and so on.

Associative arrays have no upper bound. Associative arrays contain an ordered set of zero or more elements, where each element in the array is ordered by and can be referenced by an associated index value. The data type of the index values can be an integer or a character string, but all index values for the array have the same data type.

Arrays can be used only in the following contexts:

Parameters to SQL functions
RETURN data types from SQL functions
Parameters to SQL procedures
SQL variables that are declared in SQL functions
SQL variables that are declared in SQL procedures

You can create an array by creating an array type, and then defining an array variable of that type. For example:

-- CREATE ORDINARY ARRAY TYPE INTARRAY
CREATE TYPE INTARRAY AS INTEGER ARRAY[100];
-- IN AN SQL PROCEDURE, DEFINE ARRAY INTA OF THE INTARRAY TYPE
DECLARE INTA INTARRAY;
-- CREATE ASSOCIATIVE ARRAY TYPE CHARARRAY
CREATE TYPE CHARARRAY  AS CHAR(10) ARRAY[VARCHAR(10)];
-- IN AN SQL PROCEDURE, DEFINE ARRAY CHARA OF THE CHARARRAY TYPE
DECLARE CHARA CHARARRAY;

You cannot retrieve the contents of a column directly into an array. You need to use the ARRAY_AGG function to create an array that is the intermediate result of a SELECT statement, and then retrieve the contents of that array into an SQL array variable or parameter. For example:

-- INTB IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE INTARRAY.
-- COL2 IS AN INTEGER COLUMN.
-- ARRAY_AGG RETRIEVES THE VALUES FROM COL2, AND PUTS THEM INTO AN ARRAY.
SELECT ARRAY_AGG(COL2) INTO INTB FROM TABLE1;

You can retrieve data from an array by using the UNNEST specification to assign array elements to an intermediate result table. For example:

-- IDS AND NAMES ARE ARRAYS OF TYPE INTARRAY.
INSERT INTO PERSONS(ID, NAME) 
  (SELECT T.I, T.N FROM UNNEST(IDS, NAMES) AS T(I, N));

To populate arrays, you use array constructors.

For example, this statement populates an ordinary array:

SET CHARA = ARRAY['1','2','3','4','5','6'];

For example, these statements populate an associative array, which must be populated one element at a time:

SET CANADACAPITALS['Alberta'] = 'Edmonton';
SET CANADACAPITALS['Manitoba'] = 'Winnipeg';
SET CANADACAPITALS['Ontario'] = 'Toronto';
SET CANADACAPITALS['Nova Scotia'] = 'Halifax';

A number of built-in functions are available for manipulating arrays. They are:

ARRAY_DELETE: Deletes elements from an array.
ARRAY_FIRST: Returns the minimum array index value of an array.
ARRAY_LAST: Returns the maximum array index value of an array.
ARRAY_NEXT: Returns the next larger array index value, relative to a specified array index value.
ARRAY_PRIOR: Returns the next smaller array index value, relative to a specified array index value.
CARDINALITY: Returns the number of elements in an array.
MAX_CARDINALITY: Returns the maximum number of elements that an array can contain.
TRIM_ARRAY: Deletes elements from the end of an ordinary array.