Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 12: Accessing Complex Data Types

Home Contents Index Master Index New Book

Accessing Row-Type Data

This section describes how to query and modify data contained in typed tables and in columns that are defined on row types.

The examples used throughout this section use the row types zip_t, address_t, and employee_t, which define the employee table. Figure 12-1 shows the SQL syntax that creates the row types and table:

Figure 12-1

CREATE ROW TYPE zip_t
(
	z_code			CHAR(5),
	z_suffix			CHAR(4)
);



CREATE ROW TYPE address_t
(
	street		VARCHAR(20),
	city		VARCHAR(20),
	state		CHAR(2),
	zip		zip_t
);


CREATE ROW TYPE employee_t 
(
name		VARCHAR(30),
address		address_t
salary		INTEGER
);


CREATE TABLE employee OF TYPE employee_t;

Important: The order in which you create named row types is important because a named row type must exist before you can use it to define a table, column, or field of another named row type.

The named row types zip_t, address_t and employee_t serve as templates for the fields and columns of the employee table. The employee_t type that serves as the template for the employee table uses the address_t type as the data type of the address field. The address_t type uses the zip_t type as the data type of the zip field.

Figure 12-2 shows the SQL syntax that creates the student table. The s_address column of the student table is defined on an unnamed row type.

Figure 12-2

CREATE TABLE student 
(
s_name			VARCHAR(30),
s_address			ROW(street VARCHAR (20), city VARCHAR(20),
				state CHAR(2), zip VARCHAR(9)),
				GPA DECIMAL(3,2)
);

Selecting Columns of a Typed Table

A query on a typed table is no different than a query on any other table. For example, Query 12-1 uses the asterisk symbol (*) to construct an implicit SELECT statement that returns all columns of the employee table.

Query 12-1

SELECT * 
FROM employee

The implicit SELECT statement on the employee table returns all rows for all columns and fields, as Query Result 12-1 shows.


	Query Result 12-1

Query 12-2 shows how to construct a query that returns rows for the name and address columns of the employee table.

Query 12-2

SELECT name, address 
FROM employee
 





Query Result 12-2

For information about how to select data from supertables in a table hierarchy, see "Selecting Rows from a Supertable".

Using an Alias for a Typed Table

You can specify an alias for a table name in a SELECT or UPDATE statement and then use the alias as an expression by itself. For example, suppose you create a function foo() that accepts an argument of type employee_t and returns a Boolean value. Query 12-3 shows how you can construct a query that creates an alias e for the employee table. The table alias e is then used as the argument type for function foo(). Where foo() returns true, the query returns an entire row from the employee table.

Query 12-3

SELECT e FROM employee e
WHERE foo(e) 





Query Result 12-3

Selecting Columns That Contain Row-Type Data

You can use named row types or unnamed row types to define columns in a table. In either case, the SELECT statements that you can use are the same. The output of a query on a column is the same whether the data returned is of a named row type or unnamed row type.

When a table contains a column that is defined on a row type, a query on the column returns data from all the fields that the column contains. For example, the address column of the employee table is of type address_t, which contains four fields: address, city, state, and zip. Query 12-4 shows how to construct a query that returns all fields of the address column from the employee table.

Query 12-4

SELECT address 
FROM employee 





Query Result 12-4

To access individual fields that a column contains, you use single-dot notation to project the individual fields of the column. For example, suppose you want to access specific fields from the address column of the employee table. The following SELECT statement projects the city and state fields from the return value of the address column.

Query 12-5

SELECT address.city, address.state 
FROM employee 

 





Query Result 12-5

You construct a query on a column that contains an unnamed row type in the same way you construct a query on a column that contains a named row type. For example, suppose you want to access data from the address column of the student table that Figure 12-2 shows. You can use dot notation to query the individual fields of a column that are defined on an unnamed row type. Query 12-6 shows how to construct a SELECT statement on the student table that returns rows for the city and state fields of the s_address column.

Query 12-6

SELECT s_address.city, s_address.state 
FROM student 





Query Result 12-6

Field Projections

Do not confuse fields with columns. Columns are only associated with tables, and column projections use conventional dot notation of the form name_1.name_2 for a table and column, respectively. With the addition of row types (and the capability to assign a row type to a single column), you can reference individual fields in a column with single dot notation of the form: name_a.name_b.name_c.name_d. Informix uses the following precedence rules to interpret dot notation:

1. schema name_a . table name_b . column name_c . field name_d

2. table name_a . column name_b . field name_c . field name_d

3. column name_a . field name_b . field name_c . field name_d

When the meaning of a particular identifier is ambiguous, Universal Server uses precedence rules to determine which database object the identifier specifies. Consider the following two tables:

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2));
CREATE TABLE c (d INTEGER);

In the following SELECT statement, the expression c.d references column d of table c (rather than field d of column c in table b) because a table identifier has a higher precedence than a column identifier:

SELECT * 
FROM b,c 
WHERE c.d = 10

To reduce the risk of referencing the wrong database object, you can specify the full notation for a field projection. Suppose, for example, you want to reference field d of column c in table b (not column d of table c). The following statement specifies the table, column, and field identifiers of the object you want to reference:

SELECT * 
FROM b,c 
WHERE b.c.d = 10

Tip: Although precedence rules greatly reduce the chance of the database server misinterpreting field projections, Informix recommends that you use unique names for all table, column, and field identifiers.

Selecting Nested Fields

When the row type that defines a column itself contains other row types, the column contains nested fields. To access nested fields within a column, you use dot notation. For example, the address column of the employee table contains the fields: address, city, state, zip. In addition, the zip field contains the nested fields: z_code and z_suffix. (You might want to review the row type and table definitions that Figure 12-1 shows.) A query on the zip field returns rows for the z_code and z_suffix fields. However, you can specify that a query returns only specific nested fields. Query 12-7 shows how to construct a SELECT statement that returns rows of the z_code field of the address column only.

Query 12-7

SELECT address.zip.z_code 
FROM employee  





Query Result 12-7

INSERT INTO employee
VALUES ('Poole, John', 

ROW('402 High St', 'Willits', 'CA', 
ROW('69055','1450'))::address_t,
	35000 )

Because the address column of the employee table is a named row type, you must use a cast operator and the name of the row type to cast the row column value to type address_t.

When you use a named row type to define a column, by default, the fields of the column can contain null values. For example, the following statement is allowed:

INSERT INTO employee
VALUES (
		'Singer, John',

		ROW(NULL, 'Davis', 'CA', ROW(NULL, NULL))::address_t, 

		67000)

Inserting Rows That Contain Unnamed Row Types

The following statement shows you how to add a row to the student table, which contains a column that is an unnamed row type:

INSERT INTO student
VALUES (
	'Keene, Terry', 

	ROW('53 Terra Villa', 'Wheeling', 'IL', '45052'), 
	3.75)

When you use an unnamed row type to define a column, the fields of the column can contain null values. For example, the following INSERT statement specifies null values for the street and zip fields of the address column:

INSERT INTO student
VALUES (
	'Dorf, Beatrice', 

	ROW(NULL, 'Redding', 'CA', NULL), 

	3.50)

Updating Rows That Contain Named Row Types

To update a column that is defined on a named row type, you must specify all fields of the row type. For example, the following statement updates only the street and city fields of the address column in the employee table, but each field of the row type must contain a value (null values are allowed):

UPDATE employee
SET address = ROW('103 California St', 

'San Francisco', address.state, address.zip)::address_t
WHERE name = 'zawinul, joe'

In this example, the values of the state and zip fields are read from and then immediately reinserted into the row. Only the street and city fields of the address column are updated. To update the fields of a column that are defined on a named row type, always specify the name of the row type before the field values to be inserted.

Updating Rows That Contain Unnamed Row Types

To update a column that is defined on an unnamed row type, you must specify all fields of the row type. For example, the following statement updates only the street and city fields of the address column in the student table, but each field of the row type must contain a value (null values are allowed):

UPDATE student
SET address = ROW('13 Sunset', 'Fresno', address.state, 
address.zip)
WHERE s_name = 'henry, john'

To update the fields of a column that are defined on an unnamed row type, always specify the ROW constructor before the field values to be inserted.

Deleting Rows That Contain Row Types

You can use a WHERE clause in a DELETE statement to determine which row or rows of the table to delete. When a row contains a column that is defined on a row type, you can use dot notation to specify that only rows in which a column with a specific field value is deleted. For example, the following statement deletes only those rows from the employee table in which the value of the city field in the address column is San Jose:

DELETE FROM employee
WHERE address.city = 'San Jose'

In the preceding statement, the address column might be a named row type or an unnamed row type. The syntax you use to specify field values of a row type is the same.

Informix Guide to SQL: TutorialChapter 12: Accessing Complex Data Types Home Contents Index Master Index New Book