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