Working with Composite Types

In This Topic

dotConnect for PostgreSQL fully supports PostgreSQL composite types. dotConnect for PostgreSQL allows you to treat these types in convenient way with special classes. This article describes what are PostgreSQL composite types and how you can use them in dotConnect for PostgreSQL.

As stated in PosgtreSQL server documentation, composite type describes the structure of a row or record. It is actually just a list of field names and their datatypes. Values of composite types can be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type. When a table is created on the server, a composite type with same name is automatically created as well, so you can use it later to declare particular columns in other tables. Complete functionality for user-created composite types is introduced in PostgreSQL server 8.0. Server versions older than 8.0 have some restrictions on use of composite rows. Samples in this article assume that you work with 8.0 server.

The following SQL statement shows how to declare a composite type:

CREATE TYPE tperson AS (name text, age integer)

Later on you can use the created type as any other type on the server. For example, you can create another composite type that contains a field of type tperson. Below is code sample that demonstrates how to declare the type and create a table that uses it.

To insert some data into the table 'personnel' you can use either ROW constructor or quoted expressions. Both ways are presented below. Notice that the second and the third statements are equivalent. It is up to you to decide which way to use. Bear in mind, however, that quoting nested composite rows results in extra amount of quote symbols.

INSERT INTO personnel VALUES (1, ROW('Peter',36))
INSERT INTO personnel VALUES (2, ROW('Mark',NULL))
  or
INSERT INTO personnel VALUES (2, '("Mark",)')

Retrieving data from tables with composite rows can be as easy as fetch of common data types. If you have created type and table as described above and inserted some values, you can retrieve the data using the following code:

static void ReadData(PgSqlConnection conn)
{
  string str = "SELECT * FROM personnel";
  PgSqlCommand pgCommand = new PgSqlCommand(str,conn);
  PgSqlDataReader pgReader = pgCommand.ExecuteReader();
  while (pgReader.Read())
  {
    Console.Write(pgReader.GetInt32(0) + "\t");
    PgSqlRow pgRow = pgReader.GetPgSqlRow(1);
    if (pgRow == null)
    {
      Console.WriteLine();
      continue;
    }
    Console.Write(pgRow[0] + "\t");
    Console.WriteLine(pgRow["age"]);
  }
  pgReader.Close();
}

Visual Basic


Sub ReadData(ByVal conn As PgSqlConnection)
  Dim str As String = "SELECT * FROM personnel"
  Dim pgCommand As PgSqlCommand = New PgSqlCommand(str, conn)
  Dim pgReader As PgSqlDataReader = pgCommand.ExecuteReader()
  While pgReader.Read()
    Console.Write(pgReader.GetInt32(0) & Chr(9))
    Dim pgRow As PgSqlRow = pgReader.GetPgSqlRow(1)
    If pgRow Is Nothing Then
      Console.WriteLine()
    Else
      Console.Write(pgRow(0) & Chr(9))
      Console.WriteLine(pgRow("age"))
    End If
  End While
  pgReader.Close()
End Sub

As you can see, using PostgreSQL composite types does not complicate things much. dotConnect for PostgreSQL utilizes four entities to operate composite types and their values. These are:

PgSqlRow Class that contains data for certain cell in a table.
PgSqlRowType Class that describes composite type.
PgSqlAttribute Class that helps to describe a composite type representing type columns as attributes.
PgSqlAttributeCollection Class that holds all attributes of a composite type.

To understand how these classes cooperate consider the following code. It writes to console all fields in a composite type regardless of how many fields were declared in this type.

static void ReadData(PgSqlConnection conn)
{
  string str = "SELECT person FROM personnel";
  PgSqlCommand pgCommand = new PgSqlCommand(str,conn);
  PgSqlDataReader pgReader = pgCommand.ExecuteReader();
  while (pgReader.Read())
  {
    //obtain data
    PgSqlRow pgRow = pgReader.GetPgSqlRow(0);
    if (pgRow == null)
    {
      Console.WriteLine();
      continue;
    }
    //obtain data description
    PgSqlRowType pgRowType = pgRow.RowType;
    //iterate through all columns
    foreach(PgSqlAttribute pgAttr in pgRowType.Attributes)
    {
      Console.Write(pgRow[pgAttr] + "\t");
    }
    Console.WriteLine();
  }
  pgReader.Close();
}

Visual Basic


Sub ReadData(ByVal conn As PgSqlConnection)
  Dim str As String = "SELECT person FROM personnel"
  Dim pgCommand As PgSqlCommand = New PgSqlCommand(str, conn)
  Dim pgReader As PgSqlDataReader = pgCommand.ExecuteReader()
  While pgReader.Read()
    'obtain data
    Dim pgRow As PgSqlRow = pgReader.GetPgSqlRow(0)
    If pgRow Is Nothing Then
      Console.WriteLine()
    Else
      'obtain data description
      Dim pgRowType As PgSqlRowType = pgRow.RowType
      'iterate through all columns
      For Each pgAttr As PgSqlAttribute In pgRowType.Attributes
        Console.Write(pgRow(pgAttr) & Chr(9))
      Next pgAttr
      Console.WriteLine()
    End If
  End While
  pgReader.Close()
End Sub

dotConnect for PostgreSQL allows you to communicate with server using composite rows as parameters. This is performed in usual way. The following sample code inserts data into the table created above.

static void InsertDataWithParameters(PgSqlConnection conn)
{
  string str = "INSERT INTO personnel VALUES (:ID, :ROW)";
  PgSqlCommand pgCommand = new PgSqlCommand(str,conn);
  //Create instance of PgSqlRow and fill with data
  //Note that connection must be open to use this form of constructor
  PgSqlRow pgRow = new PgSqlRow("tperson",conn);
  pgRow[0] = "Fred";
  pgRow[1] = 24;
  //provide parameters to command and execute it
  pgCommand.Parameters.Add("ID",3);
  pgCommand.Parameters.Add("ROW",pgRow);
  pgCommand.ExecuteNonQuery();
  Console.WriteLine("Record added");
}

Visual Basic


Sub InsertDataWithParameters(ByVal conn As PgSqlConnection)
  Dim str As String = "INSERT INTO personnel VALUES (:ID, :ROW)"
  Dim pgCommand As PgSqlCommand = New PgSqlCommand(str, conn)
  'Create instance of PgSqlRow and fill with data
  'Note that connection must be open to use this form of constructor
  Dim pgRow As PgSqlRow = New PgSqlRow("tperson", conn)
  pgRow(0) = "Fred"
  pgRow(1) = 24
  'provide parameters to command and execute it
  pgCommand.Parameters.Add("ID", 3)
  pgCommand.Parameters.Add("ROW", pgRow)
  pgCommand.ExecuteNonQuery()
  Console.WriteLine("Record added")
End Sub

As you can see, it is very easy to work with composite values in dotConnect for PostgreSQL. This article has introduced the main features you should be acquainted with. In addition there are extra capabilities you can take advantage of, such as data parsing, support of nested composite types and metadata caching. These features are described in reference for classes that you can find in See Also list.