userver: uPg: PostgreSQL user type mappings
Loading...
Searching...
No Matches
uPg: PostgreSQL user type mappings

This section describes advanced usage of PostgreSQL type system with custom user-povided 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:

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 <>
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 <>
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 {
} // 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>
template <typename 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 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());