Selecting All Fields of a ROW Column with Asterisk Notation

If you want to select all fields of a column that has a ROW type, you can specify the column name without using dot notation. For example, you can select all fields of the rect column as follows:

SELECT rect FROM rectangles
   WHERE area = 64;

You can also use asterisk ( * ) notation to project all the fields of a column that has a ROW data type. For example, if you want to use asterisk notation to select all fields of the rect column, you can enter the following statement:

SELECT rect.* FROM rectangles
   WHERE area = 64;

Asterisk notation is easier than specifying each field of the rect column individually:

SELECT rect.x, rect.y, rect.length, rect.width
   FROM rectangles
      WHERE area = 64;

Asterisk notation for ROW fields is valid in the projection list of a SELECT statement. It can specify all fields of a ROW-type column or the data that a ROW-column expression returns.

Asterisk notation is not necessary with ROW-type columns, because you can specify the column name alone to project all of its fields. Asterisk notation is quite helpful, however, with ROW-type expressions such as subqueries and user-defined functions that return ROW-type values. For more information, see Using Dot Notation with Row-Type Expressions.

You can use asterisk notation with columns and expressions of ROW data types in the projection list of a SELECT statement only. You cannot use asterisk notation with columns and expressions of ROW type in any other clause of a SELECT statement.