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:
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:
The following code allows retrieval of that 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:
The following C++ type should be written:
Now it is possible to select the datatype directly into a C++ type:
A connection gets the data types' definitions after connect and uses the definitions to map C++ types to PostgreSQL type oids.
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:
and a C++ enumeration declared as follows:
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.
After the above steps the enum is ready to be used:
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.
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.
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.
and declare a mapping from C++ range to this type just as for any other user type:
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:
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:
Mapping of the above type to a strong typedef with name TimeOfDay could be done in the following way:
The actual usage: