fix SQL expressions

[gdrolet]

The way SQL expressions were formatted kept from retrieving geometries from table.

[dnbucklin]

@gdrolet,

Hi, could you provide some information about what was causing the issue you were fixing here (e.g., the function call you were using)? What is the table name? Thanks.

[gdrolet]

Problem was with the database driver I use (RPostgres::Postgres()):

> conn <- RPostgreSQL::dbConnect(
    RPostgres::Postgres(),
    dbname = dbname,
    host = host,
    user = user
)
> class(conn)
[1] "PqConnection"
attr(,"package")
[1] "RPostgres"

> name <- c("public", "plots_7m")
> plots <- rpostgis::pgGetGeom(conn, name)

Note: method with signature ‘PqConnection#character’ chosen for function ‘dbQuoteIdentifier’,
 target signature ‘PqConnection#SQL’.
 "DBIConnection#SQL" would also be valid
Error in rpostgis::pgGetGeom(conn, name) : 
  Table/view '"public"."plots_7m"' geometry column not found. Available geometry columns: 

It's ok when I use RPostgreSQL::PostgreSQL() driver,

> conn <- RPostgreSQL::dbConnect(
    RPostgreSQL::PostgreSQL(),
    dbname = dbname,
    host = host,
    user = user
 )
> class(conn)
[1] "PostgreSQLConnection"
attr(,"package")
[1] "RPostgreSQL"

> plots <- rpostgis::pgGetGeom(conn, name)
Returning Point types in SpatialPoints*-class.

Using my fix allows using PostgreSQLConnection or PqConnection connection.

[dnbucklin]

Thanks for bringing this to our attention. We hadn't tested using that driver, but it will be something we'll want to make sure we have compliance for following the RPostgres release to CRAN.

I made a commit to our develop branch to address this specific issue related to quoting of schema and table names. If you could install and use that version of rpostgis with the pqConnection driver, and report any other potential issues you might run into on our Issues board, that would be useful!

To install it:

devtools::install_github("mablab/rpostgis",ref = "develop")