The typed collection variable

The following figure shows declarations for three 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;

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;

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;

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.

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;