The typed collection variable

A typed collection variable provides an exact description of the collection. This declaration specifies the data type of the collection (SET, MULTISET, or LIST) and the element type for the collection variable.

The following figure shows declarations for three typed collection variables.
Figure 1: Sample typed collection variables
EXEC SQL BEGIN DECLARE SECTION;
   client collection list(smallint not null)
      list1;
   client collection set(row(
      x char(20), 
      y set(integer not null),
      z decimal(10,2)) not null) row_set;
   client collection multiset(set(smallint 
         not null) 
      not null) collection3;
EXEC SQL END DECLARE SECTION;
Typed collection variables can contain elements with the following data types:
  • Any built-in data type (such as INTEGER, CHAR, BOOLEAN, and FLOAT) except BYTE, TEXT, SERIAL, or SERIAL8.
  • Collection data types, such as SET and LIST, to create a nested collection
  • Unnamed row types (named row types are not valid)
  • Opaque data types
When you specify the element type of the collection variable, use the SQL data types, not the data types. For example, as the declaration for the list1 variable in Sample typed collection variables illustrates, use the SQL SMALLINT data type, not the short data type, to declare a LIST variable whose elements are small integers. Similarly, use the SQL syntax for a CHAR column to declare a SET variable whose elements are character strings, as the following example illustrates:
client collection set(char(20) not null) set_var;
Important: You must specify the not-null constraint on the element type of a collection variable.

A named row type is not valid as the element type of a collection variable. However, you can specify an element type of unnamed row type, whose fields match those fields of the named row type.

For example, suppose your database has the named row type, myrow, and the database table, mytable, that are defined as follows:
CREATE ROW TYPE myrow
(
   a int,
   b float
);
CREATE TABLE mytable
(
   col1 int8,
   col2 set(myrow not null)
);
You can define a collection variable for the col2 column of mytable as follows:
EXEC SQL BEGIN DECLARE SECTION;
   client collection set(row(a int, b float) not null)
      my_collection;
EXEC SQL END DECLARE SECTION;

You can declare a typed collection variable whose element type is different from that of the collection column as long as the two data types are compatible. If the database server is able to convert between the two element types, it automatically performs this conversion when it returns the fetched collection.

Suppose you create the tab1 table as follows:
CREATE TABLE tab1 (col1 SET(INTEGER NOT NULL))
You can declare a typed collection variable whose element type matches (set_int) or one whose element type is compatible (set_float), as follows:
EXEC SQL BEGIN DECLARE SECTION;
   client collection set(float not null) set_float;
   client collection set(integer not null) set_int;
EXEC SQL END DECLARE SECTION;

EXEC SQL declare cur1 cursor for select * from tab1;
EXEC SQL open cur1;
EXEC SQL fetch cur1 into:set_float;
EXEC SQL fetch cur1 into :set_int;

When it executes the first FETCH statement, the client program automatically converts the integer elements in the column to the float values in the set_float host variable. The program only generates a type-mismatch error if you change the host variable after the first fetch. In the preceding code fragment, the second FETCH statement generates a type-mismatch error because the initial fetch has already defined the element type as float.

Use a typed collection variable in the following cases:
  • When you insert into a derived table ( needs to know what the type is)
  • When you update an element in a derived table ( needs to know what the type is)
  • When you want the server to perform a cast. (The client sends the type information to the database server, which attempts to perform the requested cast operation. If it is not possible, the database server returns an error.)
Match the declaration of a typed collection variable exactly with the data type of the collection column. You can then use this collection variable directly in SQL statements such as INSERT, DELETE, or UPDATE, or in the collection-derived table clause.
Tip: If you do not know the exact data type of the collection column you want to access, use an untyped collection variable.
In a single declaration line, you can declare several collection variables for the same typed collection, as the following declaration shows:
EXEC SQL BEGIN DECLARE SECTION;
   client collection multiset(integer not null) mset1, mset2;
EXEC SQL END DECLARE SECTION;

You cannot declare collection variables for different collection types in a single declaration line.