Support special types

[etiennebr]

The current way to support new types seems to hard code the typelem in PqResult.h. For types from extensions (such as Postgis), it seems the typelem returned can be quite variable (see #114 as an example). I believe the typelem is not a garantee of the typname in PostgreSQL; they are maintained in pg_types table (select * from pg_type;).

My understanding is that to support special types and facilitate extending RPostgres, it would require to have a generic type conversion function that could return the typelem along with R type. I'm brainstorming here, but maybe passing something like e.g. structure(c("A", "B"), class=c("18", "character")), where "18" is PostgreSQL typelem of CHAR, to a generic finalize_type() could work. Even kinder would be to lookup the typname (and cache it) rather than provide the typelem.

The Unknown field type warning would only be thrown by the generic finalize_type. But the important part is that I couldn't find a way to access the typelem from outside, and I believe it is required to support special types.

[krlmlr]

Thanks for raising this issue. We could implement something specifically for RPostgres, but I wonder how this can be generalized across different DBI backends.

I suggest we look at the concrete example first (round-tripping of geometry columns, #114), before trying to devise a generic extensible way.

[krlmlr]

Could libpqtypes help? (I haven't looked yet.)

http://libpqtypes.esilo.com/

https://github.com/r-dbi/RPostgres/issues/35#issuecomment-265235650

[etiennebr]

I agree with your concern on portability. It's the whole purpose of DBI! I don't know enough about other databases to say if it is relevant outside of Postgres. A quick look at libpqtype seems to indicate it could be useful, although it might also be overkill.

As for the round tripping (#114), with the exception of supporting special types, most of the work is performed by sf. I have a branch standing by that can write geometries to Postgres. For reading, there's still the issue of grabbing the geometry column, but if we tolerate a warning, I believe we can say it works. Most of the adaptation is on sf side, and I think it could be made easier by RPostgres. Maybe you or @hadley had something else in mind to declare round-tripping complete, though.

I've played with the idea of finalizing columns, here's how it look:

library(DBI)
library(sf)
#> Linking to GEOS 3.5.1, GDAL 2.1.3, proj.4 4.9.2, lwgeom 2.3.3 r15473

pg <- dbConnect(RPostgres::Postgres())

# there are many ways to access types, `pg_type` table is one of them
.postgres_get_typelem <- function(conn, table = "pg_type") DBI::dbReadTable(conn, table)
postgres_get_typelem <- memoise::memoise(.postgres_get_typelem)

postgres_match_typname <- function(conn, type, prefix = "pg:") {
	suppressWarnings(te <- postgres_get_typelem(conn))
	x <- with(te, typname[typelem == type & typarray == 0])
	paste0(prefix, x)
}

parse_warning <- function(x) {
	id <- stringr::str_extract(x, "\\((\\d+)\\)")
	id <- as.integer(gsub("\\(|\\)", "", id))
	
	col <- stringr::str_extract(x, "\\w+$")
	return(list(id = id, column = col))
}

# Make sure postgis is activated
dbExecute(pg, "CREATE EXTENSION postgis;")

This is the actual situation, but it still can be manually cast to sf.

x <- dbGetQuery(pg, "SELECT 'POINT(0  0)'::geometry;")
#> Warning in result_create(conn@ptr, statement): Unknown field type (48892)
#> in column geometry

Following is a hack to grab and parse the warning just to mimic the behavior it could have,
hopefully, we can come up with a better solution

cw <- testthat::capture_warnings(pts <- dbGetQuery(pg, 
            "SELECT 1 as num, 'POINT(0  0)'::geometry;"))
w <- parse_warning(cw)
tp <- postgres_find_typname(pg, w$id)

# Setting attributes and a generic finalize could be performed after 
# reading the table within `RPostgres`|`DBI` 
pts[[w$column]] <- structure(pts[[w$column]], class = c(tp, class(pts[[w$column]])))

setGeneric("db_finalize_col", function(.) return(.))

# Packages can then access the finalization
setClass("pg:_geometry")
setMethod("db_finalize_col", "pg:_geometry", 
        function(.) sf::st_as_sfc(structure(., class = "WKB")))

# DBI just has to finalize each column
pts <- dplyr::bind_cols(lapply(pts, db_finalize_col))

pts
#> # A tibble: 1 x 2
#>     num         geometry
#>   <int> <simple_feature>
#> 1     1    <POINT (0 0)>

# In the end, it would still require explicit cast to `sf`, 
# but it's very functional even as a tibble
sf::st_as_sf(pts)
#> Simple feature collection with 1 feature and 1 field
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 0 ymin: 0 xmax: 0 ymax: 0
#> epsg (SRID):    NA
#> proj4string:    NA
#> # A tibble: 1 x 2
#>     num         geometry
#>   <int> <simple_feature>
#> 1     1    <POINT (0 0)>

Hopefully this is concrete enough to help design a generic extensible way, but I'd be pleased to provide more examples.

[krlmlr]

Closing in favor of https://github.com/r-dbi/DBI/issues/203.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.