Supported assignments to row data types

A variety of values are supported for assignment to rows and row fields.

When a row variable or parameter is declared, each field in the row has a default value of NULL until a value is assigned to it.

The following types of values can be assigned to a row variable:
  • another row variable of the same row data type using the SET statement

    • Row variable values can only be assigned to row variables if they are type compatible. Two row variables are compatible if they are both of the same row data type or if the source row variable is anchored to a table or view definition. For two variables to be type compatible, it is not sufficient for them to have the same field names and field data types.

      For example, if a row data type named row1 is created and another data type named row2 is created and they are identical in definition, the value of a variable of type row1 cannot be assigned to the variable of type row2. Nor can the value of the variable of type row2 be assigned to the variable of type row1. However, the value of variable v1 of type row1 can be assigned to a variable v2 that is also of type row1.

  • A tuple with the same number of elements as the row and elements of the same data types as the fields of the row.
    • The following is an example of a literal tuple being assigned to a row:
      SET v1 = (1, 'abc')
  • expression that resolves to a row value
    • An example of an expression that resolves to a row value that can be assigned to a row variable is the resolved expression in a VALUES ... INTO statement. The following is an example of such an assignment:
      VALUES (1, 'abc') INTO rv1
  • the return type of a function (if it is of the same row data type as the target variable):
    • The following is an example where the return type of a function named foo is of the same row data type as the target variable:
      SET v1 = foo()
      If the return data type is defined as an anchored data type, the anchored data type assignment rules apply.
  • the single row result set of a query
    • The result set must have the same number of elements as the row and the columns must be assignable to the same data types as the fields of the row. The following is an example of this type of assignment: 
      SET v1 = (select c1, c2 from T)
  • NULL
    • When NULL is assigned to a row variable, all the row fields are set to NULL but the row variable itself remains NOT NULL.
The following types of values can be assigned to a row variable field:
  • literal
  • parameter
  • variable
  • expression
  • NULL
Values can be assigned to row field values in the following ways:
  • Using the SET statement
  • Using a SELECT INTO statement that resolves to a row value
  • Using a FETCH INTO statement that resolves to a row value
  • Using a VALUES INTO statement that resolves to a row value

The ROW data type can be specified as the return-type of an SQL scalar function.