The typed row variable

A typed row variable specifies a field list, which contains the name and data type of each field in the row.

The following figure shows declarations for three typed row variables.
Figure 1: Sample typed row variables
EXEC SQL BEGIN DECLARE SECTION;
  row (circle_vals circle_t, circle_id integer) mycircle;
  row (a char(20), 
      b set(integer not null),
      c decimal(10,2)) row2;
  row (x integer,
      y integer,
      length integer,
      width integer) myrect;
EXEC SQL END DECLARE SECTION;
Typed row variables can contain fields with the following data types:
  • Any built-in data type (such as INTEGER, CHAR, BOOLEAN, and FLOAT) except BYTE, TEXT, SERIAL, or SERIAL8.
  • Collection data types, such as SET and LIST
  • Row types, named or unnamed
  • Opaque data types
When you specify the type of a field in the row variable, use SQL data types, not data types. For example, to declare a row variable with a field that holds small integers, use the SQL SMALLINT data type, not the int data type. Similarly, to declare a field whose values are character strings, use the SQL syntax for a CHAR column, not the C syntax for char variables. For example, the following declaration of the row_var host variable contains a field of small integers and a character field: 
row (
   smint_fld smallint,
   char_fld char(20)
   ) row_var;

Use a typed row variable when you know the exact data type of the row-type column that you store in the row variable. Match the declaration of a typed row variable exactly with the data type of the row-type column. You can use this row variable directly in SQL statements such as INSERT, DELETE, or UPDATE. You can also use it in the collection-derived table clause.

You can declare several row variables in a single declaration line. However, all variables must have the same field types, as the following declaration shows: 
EXEC SQL BEGIN DECLARE SECTION;
   row (x integer, y integer) typed_row1, typed_row2;
EXEC SQL END DECLARE SECTION;

If you do not know the exact data type of the row-type column you want to access, use an untyped row variable.