Subset of Complex Data Types

To store one or more data values in a complex data type, the DEFINE statement can declare a local SPL variable as a collection of type SET, MULTISET, or LIST, with elements of a specified type, or as a generic COLLECTION. The DEFINE statement can also declare a local SPL variable as a named or an unnamed ROW type with an ordered set of fields, or as a generic ROW type.

Complex Data Types (Subset)

1 COLLECTION
1 
2.1 SET
2.1 MULTISET
2.1 LIST
1 (
2.1 data_type
2.1 
2.2.1 SET
2.2.1 MULTISET
2.2.1 LIST
2.1  ( data_type NOT NULL )
1 NOT NULL)
1 
2.1 row
2.1  ROW?  ( + , field data_type )
Element Description Restrictions Syntax
data_type Data type of collection elements, or of fields in an unnamed ROW type Must match the data type of the values that the variable will store. Cannot be BIGSERIAL, BLOB, BYTE, CLOB, SERIAL, SERIAL8, or TEXT. Data Type
field Field of an unnamed ROW If you specify no field list, the generic ROW variable can store data of any ROW type Identifier
row Named ROW data type Must exist in the database Identifier

Example of a named ROW type variable

The following statement registers in the database a named ROW data type that can store two non-NULL integer values, and declares two_whole_numbers_t as its identifier:
CREATE ROW TYPE IF NOT EXISTS
   two_whole_numbers_t( x INT NOT NULL, y INT NOT NULL);
Because this CREATE ROW TYPE statement specifies no supertype, the new named ROW type inherits no fields from any existing ROW type hierarchy.
This DEFINE statement declares numbers as an SPL variable whose data type is the two_whole_numbers_t named ROW type: 
DEFINE numbers two_whole_numbers_t;   -- named ROW type
In the statement above, the name of this ROW type identifies the structure of the numbers variable as having two fields of type INT that can store only non-NULL values.

Example of a generic ROW type variable

The next example assigns to the variable whatever a generic ROW data type, without field declarations:
DEFINE whatever ROW;   -- generic ROW type
If an SPL function included both of these DEFINE statements, the numbers variable could return two integers as the result of the SPL routine, but the results cannot be returned to the calling context by the whatever variable.
Important:
Generic ROW and generic COLLECTION types are not supported as return data types of SPL functions. In the example above, the SPL routine must cast the fields of the generic ROW variable whatever into specific SQL data types.

Similarly, if elements of a generic COLLECTION variable store the routine results, those values must be cast into specific SQL data types that the SPL routine can return.