User defined datatypes: GeoPoint-2D

[Tectu]

I'm interacting with a PostgreSQL database via SOCI. One of the database tables uses the point type to represent a 2D point (OID 600). This type consists of two floats. Doing a simple SELECT * FROM mytable results in SOCI being unable to work with that column:

unknown data type with typelem: 600 for colNum: 13

I read through the documentation regarding implementing support for custom/user types. Internally (within my application), the type can be represented as:

struct point
{
    float x;
    float y;
};

However, I currently fail understanding how I would go about implementing the from_base() and to_base() specializations for this. Most importantly, I am not sure which type base_type would be defined to. How would I go about implementing from_base() for a type like this? What type would the first parameter be? Clearly I can't use float as I have to deal with two floats.

I guess my question boils down to: "How can I write a custom type conversion for a database type that is consisting of more than just one value (eg. in this point case it's x, y - two values)?"

[vadz]

I could be wrong, but I don't think there is any provision for mapping a user-defined type to a database type not supported by SOCI yet, i.e. an OID not already handled by it in case of Postgres. IOW this is not something that can be done from outside the library.

We could either add some generic API allowing to do this (potentially quite useful, but I have no idea at all how could this API look like to work with all the different databases) or just add support for point (and other geometric types?) to SOCI Postgres backend leaving it unimplemented for all the other ones (which is not nearly as nice, but should probably be much simpler).

[Tectu]

Hmm, I'm afraid this is the answer I expected after having read the corresponding library source code :D

Creating a generic API for this sort of thing is - as you mentioned - not an easy task. I'll be needing this feature somewhat desperately so I think there's (currently) not really a way around introducing a new datatype (such as dt_point2d or dt_geometry_point2d (any preferences?)) and extending the PostgreSQL backend. Can you provide any information as to "how easy" or "how complex" this would be? Are there parts outside the database backend (other than the type enum) that needs to be extended/modified? I'm asking this as I'd like this to be contributable/mergable once I'm done with it.

[vadz]

Sorry, I'm afraid there is no documentation for adding support for new types (and yes, it would be useful to have...) and I can't really just explain it because I don't know all the details myself. Your best bet would be to check how one of the existing types (e.g. dt_long_long == OID 20) is handled and try to mimic it.

And if you can sum up your experience in a new section in docs/backends/postgresql.md or maybe some new docs/development.md, it would be great!

[Tectu]

I spent some minor amount of time going through the SOCI codebase and came to the conclusion that adding support for additional data types shouldn't be too hard (by adding the OID & providing the necessary infrastructure around it).

However, the project initially needing this was changed in the meantime. Unfortunately, I won't have enough time anytime soon to dive into this. Therefore, I'm closing this issue.