uPg: PostgreSQL user type mappings

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 can 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.

Note

Enums generated by codegen can be easily mapped via storages::postgres::io::Codegen.

Mapping C++ enums to PostgreSQL text

A C++ enumeration can be mapped to PostgreSQL text type by providing a ToString() and Parse() functions for the enumeration values and declaring specialization of storages::postgres::io::CppToSystemPg template.

Example:

enum class GeneratedAnimals { kCat, kDog, kBird, kFish };
 
GeneratedAnimals Parse(std::string_view value, formats::parse::To<GeneratedAnimals>);
std::string ToString(GeneratedAnimals value);
 
template <>
struct storages::postgres::io::CppToSystemPg<GeneratedAnimals>
    : storages::postgres::io::PredefinedOid<storages::postgres::io::PredefinedOids::kText> {};

Note

Enums generated by codegen can be easily mapped to postgres text type by only providing the CppToSystemPg specialization, as they already have ToString() and Parse() functions.

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 ⇨