Row Type of the Resulting Collection-Derived Table

If the data type of the elements of the underlying collection expression is type, the database server determines the ROW type of the collection-derived table by the following rules:

• If type is a ROW data type, and no derived-column list is specified, then the ROW type of the collection-derived table is type.
• If type is a ROW data type and a derived column list is specified, then the ROW type of the collection-derived table is an unnamed ROW type whose column data types are the same as those of type and whose column names are taken from the derived column list.
• If type is not a ROW data type, the ROW type of the collection-derived table is an unnamed ROW type that contains one column of type and whose name is specified in the derived column list. If no name is specified, the database server assigns an implementation-dependent name to the column.

The extended examples that the following table shows illustrate these rules. The table uses the following schema for its examples:

CREATE ROW TYPE person (name CHAR(255), id INT);
CREATE TABLE parents
   (
   name CHAR(255), 
   id INT,
   children LIST (person NOT NULL)
   );
CREATE TABLE parents2
   (
   name CHAR(255), 
   id INT,
   children_ids LIST (INT NOT NULL)
   );

The following program fragment creates a collection-derived table using an SPL function that returns a single value:

CREATE TABLE wanted(person_id int);
CREATE FUNCTION 
    wanted_person_count (person_set SET(person NOT NULL))
RETURNS INT;
RETURN( SELECT COUNT (*)
    FROM TABLE (person_set) c_table, wanted
    WHERE c_tabel.id = wanted.person_id);
END FUNCTION;

The next program fragment shows the more general case of creating a collection-derived table using an SPL function that returns multiple values:

-- Table of categories and child categories, 
-- allowing any number of levels of subcategories
CREATE TABLE CategoryChild (
         categoryId      INTEGER,
         childCategoryId SMALLINT
);

INSERT INTO CategoryChild VALUES (1, 2);
INSERT INTO CategoryChild VALUES (1, 3);
INSERT INTO CategoryChild VALUES (1, 4);
INSERT INTO CategoryChild VALUES (2, 5);
INSERT INTO CategoryChild VALUES (2, 6);
INSERT INTO CategoryChild VALUES (5, 7);
INSERT INTO CategoryChild VALUES (7, 8);
INSERT INTO CategoryChild VALUES (7, 9);
INSERT INTO CategoryChild VALUES (4, 10);

-- "R" == ROW type
CREATE ROW TYPE categoryLevelR (
         categoryId      INTEGER,
         level   SMALLINT );

-- DROP FUNCTION categoryDescendants ( 
--               INTEGER, SMALLINT );
CREATE FUNCTION categoryDescendants (
         pCategoryId INTEGER,
         pLevel      SMALLINT DEFAULT 0 )
RETURNS MULTISET (categoryLevelR NOT NULL)

-- "p" == Prefix for Parameter names
-- "l" == Prefix for Local variable names
DEFINE lCategoryId LIKE CategoryChild.categoryId;
DEFINE lRetSet MULTISET (categoryLevelR NOT NULL);
DEFINE lCatRow categoryLevelR;

-- TRACE ON;
-- Must initialize collection before inserting rows
LET lRetSet = 'MULTISET{}' :: MULTISET (categoryLevelR NOT NULL);

FOREACH
SELECT childCategoryId INTO lCategoryId 
   FROM CategoryChild WHERE categoryId = pCategoryId;
INSERT INTO TABLE (lRetSet)
   VALUES (ROW (lCategoryId, pLevel+1)::categoryLevelR);
 
-- INSERT INTO TABLE (lRetSet);
-- EXECUTE FUNCTION categoryDescendantsR ( lCategoryId,      
-- pLevel+1 );
-- Need to iterate over results and insert into SET.
-- See the SQL Tutorial, pg. 10-52:
-- "Tip: You can only insert one value at a time 
-- into a simple collection."
   FOREACH
   EXECUTE FUNCTION categoryDescendantsR ( lCategoryId, pLevel+1 )
     INTO lCatRow;
       INSERT INTO TABLE (lRetSet)
           VALUES (lCatRow);
   END FOREACH;
END FOREACH;
 
RETURN lRetSet;
END FUNCTION
;
-- "R" == recursive
-- DROP FUNCTION categoryDescendantsR (INTEGER, SMALLINT);
CREATE FUNCTION categoryDescendantsR (
         pCategoryId INTEGER,
         pLevel      SMALLINT DEFAULT 0
)
RETURNS categoryLevelR; 
DEFINE lCategoryId      LIKE CategoryChild.categoryId;
DEFINE lCatRow          categoryLevelR;
 
FOREACH   
   SELECT  childCategoryId
   INTO    lCategoryId
   FROM    CategoryChild
   WHERE   categoryId = pCategoryId
   RETURN ROW (lCategoryId, pLevel+1)::categoryLevelR WITH RESUME;
 
   FOREACH
   EXECUTE FUNCTION categoryDescendantsR ( lCategoryId, pLevel+1 )
      INTO    lCatRow
      RETURN  lCatRow WITH RESUME;
   END FOREACH;
END FOREACH; 
END FUNCTION;
 
-- Test the functions:
SELECT lev, col
FROM    TABLE ((
         categoryDescendants (1, 0)
         )) AS CD (col, lev);