Return exact Pg field type information in JSON return
pramsey opened this issue · 2 comments
Motivation: Right now clients can only infer generic type information (see below) about return fields, and they sometimes need/want more exact type information than we provide. They used to just look at the system tables to figure this stuff out, but now with read-only keys that restrict system table access, they really need to get this information directly from the result set.
End State: Clients should be able to get a fast and precise table column/type definition just by running SELECT * FROM mytable LIMIT 0.
Right now we return loose field information:
"fields" : {
"the_geom" : {
"type" : "geometry"
},
"created_at" : {
"type" : "date"
},
"the_geom_webmercator" : {
"type" : "geometry"
},
"id" : {
"type" : "number"
},
"updated_at" : {
"type" : "date"
},
"cartodb_id" : {
"type" : "number"
}
}
But we could return the exact type information, which OGR uses to form the field definitions when creating OGRLayer objects in the CARTO driver. The OGR types distinguish between real/integer, date and datetime. They also distinguish in geometries between the geometry types and the dimensionality of geometries (2d/3d) and the SRS of geometries.
We can extract the type/dim/srid information from the typmod number returned in libpq via PQfmod(const PGresult *res, int field_num), as it all lives in various bitfields of the integer.
The exact postgresql types can be inferred from the Oid, there's probably a lookup for this already behind the fields output. We just need to be a little more exact. Either go for a fields_ogr output list, or add a type_ogr entry to the existing fields or more generically just add a pgtype field.
"fields" : {
"the_geom" : {
"type" : "geometry",
"wkbtype": "Point",
"dims": 2,
"srid": 4326
},
"created_at" : {
"type" : "date",
"pgtype": "timestamptz"
},
"the_geom_webmercator" : {
"type" : "geometry",
"wkbtype": "Point",
"dims": 2,
"srid": 3857
},
"id" : {
"type" : "number",
"pgtype": "int4"
},
"updated_at" : {
"type" : "date",
"pgtype": "timestamptz"
},
"cartodb_id" : {
"type" : "number",
"pgtype": "int4"
}
}
A fair question regarding the type/dims stuff is "why do we need that, it's implied by the column name", and the answer is that it's possible this way to read a table that has not yet been passed through the Cartodbfy() process.
Here's a first step along the way: allow us to read info off the typmod number that we can read using PQfmod(const PGresult *res, int field_num)
CartoDB/node-cartodb-psql#34