ROW Constructors

You use ROW constructors to generate values for ROW-type columns. Suppose you create the following named ROW type and a table that contains the named ROW type row_t and an unnamed ROW type:

CREATE ROW TYPE row_t ( x INT, y INT);
CREATE TABLE new_tab 
(
col1 row_t, 
col2 ROW( a CHAR(2), b INT)
)

When you define a column as a named ROW type or unnamed ROW type, you must use a ROW constructor to generate values for the ROW-type column. To create a value for either a named ROW type or unnamed ROW type, you must complete the following steps:

• Begin the expression with the ROW keyword.
• Specify a value for each field of the ROW type.
• Enclose the comma-separated list of field values within parentheses.

The format of the value for each field must be compatible with the data type of the ROW field to which it is assigned.

You can use any kind of expression as a value with a ROW constructor, including literals, functions, and variables. The following examples show the use of different types of expressions with ROW constructors to specify values:

ROW(5, 6.77, 'HMO')

ROW(col1.lname, 45000)

ROW('john davis', TODAY)

ROW(USER, SITENAME)

The following statement uses literal numbers and quoted strings with ROW constructors to insert values into col1 and col2 of the new_tab table:

INSERT INTO new_tab 
VALUES 
(
ROW(32, 65)::row_t,
ROW('CA', 34)
)

When you use a ROW constructor to generate values for a named ROW type, you must explicitly cast the ROW value to the appropriate named ROW type. The cast is necessary to generate a value of the named ROW type. To cast the ROW value as a named ROW type, you can use the cast operator ( :: ) or the CAST AS keywords, as the following examples show:

ROW(4,5)::row_t 
CAST (ROW(3,4) AS row_t)

You can use a ROW constructor to generate ROW type values in INSERT, UPDATE, and SELECT statements. In the next example, the WHERE clause of a SELECT statement specifies a ROW type value that is cast as type person_t:

SELECT * FROM person_tab 
   WHERE col1 = ROW('charlie','hunter')::person_t

For more information on using ROW constructors in INSERT and UPDATE statements, see the INSERT and UPDATE statements in this manual. For information on named ROW types, see the CREATE ROW TYPE statement. For information on unnamed ROW types, see the discussion of the ROW data type in the IBM Informix Guide to SQL: Reference. For task-oriented information on named ROW types and unnamed ROW types, see the IBM Informix Database Design and Implementation Guide.