Insert elements into LIST collections

LIST collections have elements that have ordered positions. If the collection is of type LIST, you can use the AT clause of the INSERT statement to specify the position in the list at which you want to add the new element. Suppose the table rankings has the following declaration:

CREATE TABLE rankings
(
   item_id            INT8,
   item_rankings            LIST(INTEGER NOT NULL)
);

To access the item_rankings column, the typed host variable rankings has the following declaration:

EXEC SQL BEGIN DECLARE SECTION;
   client collection list(integer not null) rankings;
   int an_int;
EXEC SQL END DECLARE SECTION;

The following INSERT statement adds a new list element of 9 as the new third element of rankings:

EXEC SQL allocate collection :rankings;
EXEC SQL select rank_col into :rankings from results;
an_int = 9;
EXEC SQL insert at 3 into table(:rankings) values (:an_int);