This section describes advanced usage of PostgreSQL type system with custom user-provided database types.

For a basic information on querying data see uPg: Running queries and uPg: Working with result sets. A list of supported fundamental PostgreSQL types and their mappings to C++ types is available at uPg: Supported data types.

In PosgtgreSQL database the following kinds of user types are available:

composite (row) types
enumerations
ranges
domains

Mapping a C++ type to PostgreSQL domain user type

Domains are essentially some database data types with database constraints applied to them. Domains map to their base data types' C++ counterparts.

For a PostgreSQL database domain defined as:

CREATE DOMAIN __pgtest.dom AS integer
  DEFAULT 42
  NOT NULL

The following code allows retrieval of that type:

auto res = connection->Execute("SELECT 5::__pgtest.dom");

EXPECT_EQ(res[0][0].As<int>(), 5);

Mapping a composite (row) user type

The driver supports user-defined PostgreSQL composite types. The C++ counterpart type must satisfy the same requirements as for the row types, (uPg: Typed PostgreSQL results) and must provide a specialization of storages::postgres::io::CppToUserPg template (pg_user_types).

After a C++ type is defined, it must be mapped to its PostgreSQL counterpart by specialising storages::postgres::io::CppToUserPg template for the type. C++ types are mapped to PostgreSQL types by their name, so the specialization for storages::postgres::io::CppToUserPg template must have a static constexpr member of type DBTypeName named postgres_name.

Parsing a composite structure from PostgreSQL buffer will throw an error if the number of fields in the postgres data is different from the number of data members in target C++ type. This is the only sanity control for the composite types. The driver doesn't check the data type oids, it's user's responsibility to provide structures with compatible data members.

For a PostgreSQL database type defined as:

CREATE TYPE __pgtest.composite AS (
  i integer,
  s text,
  d double precision
)

The following C++ type should be written:

namespace pgtest {
 
struct Composite {
    pg::Integer i;
    std::string s;
    double d;
};
 
}  // namespace pgtest
 
// This specialization MUST go to the header together with the mapped type
template <>
struct storages::postgres::io::CppToUserPg<pgtest::Composite> {
    static constexpr DBTypeName postgres_name = "__pgtest.composite";
};

Warning: The type mapping specialization must be accessible at the points where parsing/formatting of the C++ type is instantiated. The header where the C++ type is declared is an appropriate place to do it.

Now it is possible to select the datatype directly into a C++ type:

        auto res = connection->Execute("SELECT $1", pgtest::Composite{1, "hello", 4.0});
 
        auto composite = res[0][0].As<pgtest::Composite>();
        EXPECT_EQ(composite.i, 1);
        EXPECT_EQ(composite.s, "hello");

A connection gets the data types' definitions after connect and uses the definitions to map C++ types to PostgreSQL type oids.

Mapping a C++ enum to PostgreSQL enum type

A C++ enumeration can be mapped to a PostgreSQL enum type by providing a list of PostgreSQL literals mapped to the enumeration values via a specialization of storages::postgres::io::CppToUserPg template.

For example, if we have a PostgreSQL enum type:

CREATE TYPE __pgtest.rainbow AS enum (
  'red', 'orange', 'yellow', 'green', 'cyan'
)

and a C++ enumeration declared as follows:

enum class Rainbow { kRed, kOrange, kYellow, kGreen, kCyan };
 
// This specialization MUST go to the header together with the mapped type
template <>
struct storages::postgres::io::CppToUserPg<Rainbow> {
    static constexpr DBTypeName postgres_name = "__pgtest.rainbow";
    static constexpr USERVER_NAMESPACE::utils::TrivialBiMap enumerators = [](auto selector) {
        return selector()
            .Case("red", Rainbow::kRed)
            .Case("orange", Rainbow::kOrange)
            .Case("yellow", Rainbow::kYellow)
            .Case("green", Rainbow::kGreen)
            .Case("cyan", Rainbow::kCyan);
    };
};

The difference from mapping a PosgreSQL user type is that for an enumeration we need to provide a list of enumerators with corresponding literals. Note that the utils::TrivialBiMap could be reused in different parts of code.

Warning: The type mapping specialization must be accessible at the points where parsing/formatting of the C++ type is instantiated. The header where the C++ type is declared is an appropriate place to do it.

After the above steps the enum is ready to be used:

auto result = connection->Execute("select $1", Rainbow::kRed);

EXPECT_EQ(Rainbow::kRed, result[0][0].As<Rainbow>());

There is an alternative way to specialize the storages::postgres::io::CppToUserPg template without using utils::TrivialBiMap. This way is much less efficient, so it is deprecated.

enum class AnotherRainbow { kRed, kOrange, kYellow, kGreen, kCyan };
// This specialization MUST go to the header together with the mapped type
template <>
struct storages::postgres::io::CppToUserPg<AnotherRainbow> : storages::postgres::io::EnumMappingBase<AnotherRainbow> {
    static constexpr DBTypeName postgres_name = "__pgtest.rainbow";
    static constexpr Enumerator enumerators[]{
        {EnumType::kRed, "red"},
        {EnumType::kOrange, "orange"},
        {EnumType::kYellow, "yellow"},
        {EnumType::kGreen, "green"},
        {EnumType::kCyan, "cyan"},
    };
};

In the above example the specialization of storages::postgres::io::CppToUserPg derives from storages::postgres::io::EnumMappingBase for it to provide type aliases for EnumeratorList and EnumType. EnumType is an alias to the enumeration type for convenience of declaring pairs, as the enumeration can have a long qualified name.

User Range types

PostgreSQL provides a facility to represent intervals of values. They can be bounded (have both ends), e.g [0, 1], [2, 10), unbounded (at least one end is infinity or absent), e.g. (-∞, +∞), and empty.

The range that can be unbounded is modelled by storages::postgres::Range template, which provides means of constructing any possible combination of interval ends. It is very versatile, but not very convenient in most cases. storages::postgres::BoundedRange template is an utility that works only with bounded ranges.

A user can define custom range types and they can be mapped to C++ counterparts, e.g.

CREATE TYPE __pgtest.my_range AS RANGE (
  subtype = __pgtest.dom
)

and declare a mapping from C++ range to this type just as for any other user type:

namespace pgtest {
 
using MyDomain = utils::StrongTypedef<struct MyDomainTag, int>;
using MyRange = storages::postgres::Range<MyDomain>;
 
}  // namespace pgtest
 
template <>
struct storages::postgres::io::CppToUserPg<pgtest::MyRange> {
    static constexpr DBTypeName postgres_name = "__pgtest.my_range";
};

Please note that my_type must be comparable both in Postgres and in C++.

The type could be used in code in the following way:

        auto result =
            // connection->Execute("select $1", pgtest::MyRange{pgtest::MyDomain{1},
            // pgtest::MyDomain{2}});
            connection->Execute("select '[1, 2]'::__pgtest.my_range");
        auto range = result[0][0].As<pgtest::MyRange>();
        EXPECT_EQ(pgtest::MyDomain{1}, range.GetLowerBound());
        EXPECT_EQ(pgtest::MyDomain{2}, range.GetUpperBound());

Time Range and Other Widely Used Types in PostgreSQL

If you need a range of PostgreSQL float type or time type (actually any type mapped to C++ type that is highly likely used by other developers), do not specialize mapping for Range<float>, Range<double> or Range<TimeOfDay<seconds>>. Declare a strong typedef (utils::StrongTypedef) for your range or bounded range and map it to your postgres range type.

Here is an example for a range type defined in PostgreSQL as:

create type __pgtest.timerange as range(
  subtype = time
)

Mapping of the above type to a strong typedef with name TimeOfDay could be done in the following way:

namespace pgtest {
 
template <typename Duration>
using TimeRange = utils::StrongTypedef<struct MyTimeTag, pg::Range<utils::datetime::TimeOfDay<Duration>>>;
 
template <typename Duration>
using BoundedTimeRange = utils::StrongTypedef<struct MyTimeTag, pg::BoundedRange<utils::datetime::TimeOfDay<Duration>>>;
 
}  // namespace pgtest
 
template <typename Duration>
struct storages::postgres::io::CppToUserPg<pgtest::TimeRange<Duration>> {
    static constexpr DBTypeName postgres_name = "__pgtest.timerange";
};
 
template <typename Duration>
struct storages::postgres::io::CppToUserPg<pgtest::BoundedTimeRange<Duration>> {
    static constexpr DBTypeName postgres_name = "__pgtest.timerange";
};

The actual usage:

        using namespace std::chrono_literals;
 
        using Seconds = utils::datetime::TimeOfDay<std::chrono::seconds>;
        using TimeRange = pgtest::TimeRange<std::chrono::seconds>;
        using BoundedTimeRange = pgtest::BoundedTimeRange<std::chrono::seconds>;
 
        auto result = connection->Execute("select $1", TimeRange{Seconds{1s}, Seconds{2s}});
        auto range = result[0][0].As<BoundedTimeRange>();
        EXPECT_EQ(Seconds{1s}, utils::UnderlyingValue(range).GetLowerBound());

⇦ uPg: Automatic connection limit | MySQL Driver ⇨