Asterisk notation to access all fields of a row type

Asterisk notation is supported only within the select list of a SELECT statement. When you specify the column name for a row-type column in a projection list, the database server returns values for all fields of the column. You can also use asterisk notation when you want to project all the fields within a ROW type.

The following query uses asterisk notation to return all fields of the address column in the employee table.

Figure 1. Query

SELECT address.* FROM employee;

Figure 2. Query result

address   ROW(102 Ruby, Belmont, CA, 49932, 1000)
address   ROW(133 First, San Jose, CA, 85744, 4900)
address   ROW(152 Topaz, Willits, CA, 69445, 1000))
⋮

The asterisk notation makes it easier to perform some SQL tasks. Suppose you create a function new_row() that returns a row-type value and you want to call this function and insert the row that is returned into a table. The database server provides no easy way to handle such operations. However, the following query shows how to use asterisk notation to return all fields of new_row() and insert the returned fields into the tab_2 table.

Figure 3. Query

INSERT INTO tab_2 SELECT new_row(exp).* FROM tab_1

For information about how to use the INSERT statement, see Modify data.

Important: An expression that uses the .* notation is evaluated only once.