dotConnect for PostgreSQL Documentation
In This Topic
    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.

    static void CreateTable(PgSqlConnection conn)
    {
      string str = "CREATE TYPE tperson AS (name text, age integer)";
      PgSqlCommand pgCommand = new PgSqlCommand(str,conn);
      pgCommand.ExecuteNonQuery();
      Console.WriteLine("Type created");
      str = "CREATE TABLE personnel (id integer, person tperson)";
      pgCommand.CommandText = str;
      pgCommand.ExecuteNonQuery();
      Console.WriteLine("Table created");
    }
    
    
    Sub CreateTable(ByVal conn As PgSqlConnection)
      Dim str As String = "CREATE TYPE tperson AS (name text, age integer)"
      Dim pgCommand As PgSqlCommand = New PgSqlCommand(str, conn)
      pgCommand.ExecuteNonQuery()
      Console.WriteLine("Type created")
      str = "CREATE TABLE personnel (id integer, person tperson)"
      pgCommand.CommandText = str
      pgCommand.ExecuteNonQuery()
      Console.WriteLine("Table created")
    End Sub
    
    

    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();
    }
    
    
    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:

    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();
    }
    
    
    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");
    }
    
    
    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.

    See Also

    PgSqlRow Class |  PgSqlRowType Class |  PgSqlAttribute Class |  PgSqlAttributeCollection Class |  Devart.Data.PostgreSql Namespace

    See Also

    PostgreSQL Specific Features