Substitutability in typed tables

When a SELECT, UPDATE, or DELETE statement is applied to a typed table, the operation applies to the named table and all of its subtables.

For example, if you create a typed table from structured type Person_t and select all rows from that table, your application can receive not just instances of the Person type, but Person information about instances of the Employee subtype and other subtypes.

The property of substitutability also applies to subtables created from subtypes. For example, SELECT, UPDATE, and DELETE statements for the Employee subtable apply to both the Employee_t type and its own subtypes. Similarly, a column defined with Address_t type can contain instances of a US address or a Brazilian address. However, this does not mean that the UPDATE statement can change the type of a row if, for example, a Person_t row is to be updated with Employee_t data. For this to work, the Person_t row would have to be deleted, and the Employee_t row inserted as a new type.

To restrict substitutability in SELECT, UPDATE, or DELETE statements, you can use the ONLY clause. For example, UPDATE ONLY(Person) SET will update rows only in the Person table and not in its subtables.

INSERT operations, in contrast, only apply to the table that is specified in the INSERT statement. Inserting into the Employee table creates an Employee_t object in the Person table hierarchy.

You can also substitute subtype instances when you pass structured types as parameters to functions, or as the result from a function. If a function has a parameter of type Address_t, you can pass an instance of one of its subtypes, such as US_addr_t, instead of an instance of Address_t. External table functions cannot return structured type columns.

Because a column or table is defined with one type but might contain instances of subtypes, it is sometimes important to distinguish between the type that was used for the definition and the type of the instance that is actually returned at runtime. The definition of the structured type in a column, row, or function parameter is called the static type. The actual type of a structured type instance is called the dynamic type. To retrieve information about the dynamic type, your application can use the TYPE_NAME, TYPE_SCHEMA, and TYPE_ID built-in functions.