Quote relation identifiers in in_schema()

[balazsdukai]

If a schema name begins with a number (or any strange character i assume), in_schema() throws a syntax error.
Please consider quoting the schema, table names by default in in_schema(), eg. by DBI::dbQuoteIdentifier().

For example:

This doesn't connect and returns the trace below:

pand3d <- tbl(con, in_schema("3dbag_staging", "pand3d"))

Error in result_create(conn@ptr, statement) : Failed to prepare query: ERROR: syntax error at or near "3" LINE 2: FROM 3dbag_staging.pand3d AS "zzz6" ^
16.
stop(structure(list(message = "Failed to prepare query: ERROR: syntax error at or near \"3\"\nLINE 2: FROM 3dbag_staging.pand3d AS \"zzz6\"\n ^\n", call = result_create(conn@ptr, statement), cppstack = structure(list( file = "", line = -1L, stack = c("/opt/Rlibs/RPostgres/libs/RPostgres.so(Rcpp::exception::exception(char const*, bool)+0x7a) [0x7f6d41d97faa]", "/opt/Rlibs/RPostgres/libs/RPostgres.so(void Rcpp::stop<char const*&, char*>(char const*, char const*&, char*&&)+0x57) [0x7f6d41d9d427]", ...
15.
result_create(conn@ptr, statement)
14.
initialize(value, ...)
13.
initialize(value, ...)
12.
new("PqResult", conn = conn, ptr = result_create(conn@ptr, statement), sql = statement, bigint = conn@bigint)
11.
.local(conn, statement, ...)
10.
dbSendQuery(con, sql)
9.
dbSendQuery(con, sql)
8.
db_query_fields.DBIConnection(src$con, from)
7.
db_query_fields(src$con, from)
6.
vars %||% db_query_fields(src$con, from)
5.
tbl_sql(c(subclass, "dbi"), src = src, from = from)
4.
tbl.src_dbi(dbplyr::src_dbi(src, auto_disconnect = FALSE), from = from, ...)
3.
tbl(dbplyr::src_dbi(src, auto_disconnect = FALSE), from = from, ...)
2.
tbl.DBIConnection(con, in_schema("3dbag_staging", "pand3d"))
1.
tbl(con, in_schema("3dbag_staging", "pand3d"))

This connects:

pand3d <- tbl(con, in_schema(DBI::dbQuoteIdentifier(con, "3dbag_staging"), "pand3d"))

[hadley]

Minimal reprex:

library(dbplyr)
in_schema("3dbag_staging", "pand3d")
#> <IDENT> 3dbag_staging.pand3d

^{Created on 2019-05-30 by the reprex package (v0.2.1.9000)}

I think fixing this is going to be challenging, because we only know how to quote at SQL generation time, but in_schema() eagerly evaluates it's inputs. 🤔

[lorenzwalthert]

Does using dbplyr::sql_quote() have the same problem as DBI::dbQuoteIdentifier()?

[moodymudskipper]

I assume this is linked, apologies if it isn't :

I had issues connecting a schema on PostgreSQL until I realized PostgreSQL needs names to be quoted whenever some characters are upper case, and in_schema() doesn't do it by default.

It was a headache to debug but the fix was simply to use in_schema('"SCHEMA"', "table") rather than in_schema("SCHEMA", "table").

If it's tricky to implement, I think at least a note in the doc would go a long way.