SQL Reference

Dereference Operations

 
dereference-operation
 
|--scoped-ref-expression-- -> --name1--------------------------->
 
>-----+---------------------------------+-----------------------|
      '-(--+--------------------+---)---'
           |  .-,------------.  |
           |  V              |  |
           '----expression---+--'
 

The scope of the scoped reference expression is a table or view called the target table or view. The scoped reference expression identifies a target row. The target row is the row in the target table or view (or in one of its subtables or subviews) whose object identifier (OID) column value matches the reference expression. See CREATE TABLE for further information about OID columns. The dereference operation can be used to access a column of the target row, or to invoke a method, using the target row as the subject of the method. The result of a dereference operation can always be null. The dereference operation takes precedence over all other operators.

scoped-ref-expression

An expression that is a reference type that has a scope (SQLSTATE 428DT). If the expression is a host variable, parameter marker or other unscoped reference type value, a CAST specification with a SCOPE clause is required to give the reference a scope.

name1

Specifies an unqualified identifier.

If no parentheses follow name1, and name1 matches the name of an attribute of the target type, then the value of the dereference operation is the value of the named column in the target row. In this case, the data type of the column (made nullable) determines the result type of the dereference operation. If no target row exists whose object identifier matches the reference expression, then the result of the dereference operation is null. If the dereference operation is used in a select list and is not included as part of an expression, name1 becomes the result column name.

If parentheses follow name1, or if name1 does not match the name of an attribute of the target type, then the dereference operation is treated as a method invocation. The name of the invoked method is name1. The subject of the method is the target row, considered as an instance of its structured type. If no target row exists whose object identifier matches the reference expression, the subject of the method is a null value of the target type. The expressions inside parentheses, if any, provide the remaining parameters of the method invocation. The normal process is used for resolution of the method invocation. The result type of the selected method (made nullable) determines the result type of the dereference operation.

The authorization ID of the statement that uses a dereference operation must have SELECT privilege on the target table of the scoped-ref-expression (SQLSTATE 42501).

A dereference operation can never modify values in the database. If a dereference operation is used to invoke a mutator method, the mutator method modifies a copy of the target row and returns the copy, leaving the database unchanged.

Examples:

• Assume the existence of an EMPLOYEE table that contains a column called DEPTREF which is a reference type scoped to a typed table based on a type that includes the attribute DEPTNAME. The values of DEPTREF in the table EMPLOYEE should correspond to the OID column values in the target table of DEPTREF column.

    SELECT EMPNO, DEPTREF->DEPTNAME 
      FROM EMPLOYEE
  

• Using the same tables as in the previous example, use a dereference operation to invoke a method named BUDGET, with the target row as subject parameter, and '1997' as an additional parameter.

     SELECT EMPNO, DEPTREF->BUDGET('1997') 
       AS DEPTBUDGET97
       FROM EMPLOYEE