Informix Guide to SQL: Syntax Segments

The Literal Row segment specifies the syntax for literal values of named row types and unnamed row types. For syntax that allows you to use expressions that evaluate to field values, see ROW Constructors.

You can specify literal values for named row types and unnamed row types. The literal row value is introduced with a ROW constructor. The entire literal row value must be enclosed in quotes.

The format of the value for each field of the row type must be compatible with the data type of the corresponding field.

Fields of a row can be literal values for the data types in the following table.

Important: You cannot specify the simple-large-object data types (BYTE and TEXT) as the field type for a row.

Literals of an Unnamed Row Type

To specify a literal value for an unnamed row type, introduce the literal row with the ROW constructor and enclose the values in parentheses. For example, suppose you define the rectangles table, as follows:

CREATE TABLE rectangles 

(

	area FLOAT, 

	rect ROW(x INTEGER, y INTEGER, length FLOAT, width FLOAT),

)

The following INSERT statement inserts values into the rect column of the rectangles table:

INSERT INTO rectangles (rect)

	VALUES ("ROW(7, 3, 6.0, 2.0)")

To specify a literal value for a named row, type, introduce the literal row with the ROW type constructor and enclose the literal values for each field in parentheses. In addition, you can cast the row literal to the appropriate named row type to ensure that the row value is generated as a named row type. The following statements create the named row type address_t and the employee table:

CREATE ROW TYPE address_t 
(
street CHAR(20), 
city CHAR(15),

state CHAR(2),

zipcode CHAR(9)
);


CREATE TABLE employee 

(

	name CHAR(30),

	address address_t

);

The following INSERT statement inserts values into the address column of the employee table:

INSERT INTO employee (address)
VALUES  (
"ROW('103 Baker St',  'Tracy','CA', 94060)"::address_t)

If the literal value is for a nested row, specify the ROW type constructor for each row level. However, only the outermost row is enclosed in quotes. For example, suppose you create the following emp_tab table:

CREATE TABLE emp_tab

(

	emp_name CHAR(10),

	emp_info ROW( stats ROW(x INT, y INT, z FLOAT))
);

The following INSERT statement adds a row to the emp_tab table:

INSERT INTO emp_tab 
VALUES ('joe boyd', "ROW(ROW(8,1,12.0))" )

Similarly, if the row string literal contains a nested collection, only the outermost quotation marks that delineate the row string literal are necessary. No quotation marks need surround the nested collection type.

Related statements: CREATE ROW TYPE, INSERT, UPDATE, and SELECT

For information on ROW constructors, see the Expression segment. See also the Collection Literal segment.