dbUnquoteIdentifier() too stringent

[baileych]

---

It appears that the object with class "SQL" produced by dbQuoteIdentifier(pq_conn, 'some.string') surrounds the string with " which the grep() in dbUnquoteIdentifier(pq_conn, quoted_sql) sees. But the object with class "SQL" produced by DBI::SQL(dbplyr::in_schema('some', 'string')), which his how dbplyr generates the table name to pass to DBI, does not add the ", apparently because it's coming to DBI as an identifier. The absence of the double quotes dplyr's copy_to() to blow up with an RPostgres connection, via the calls to db_copy_to() and dbExistsTable().

Since dbUnquoteIdentifier() actually wants a dot-separated schema.table pair, I'm not sure why it's so fussy about the presence of the quotes. Could we consider making them optional? The operative code appears to be the regex at line 74 of RPostgres' quote.R.

An alternative might be to teach dbplyr's IDENT class handling to add quotes, but that will have a series of knock-on effects in places that assume these objects can be pasted into SQL strings - not insoluble, but would be good to understand the benefit of dbUnquoteIdentifier()'s requirement for quotation marks as a start.

require(RPostgres)
require(dplyr)
require(tibble)

copy_to(dbConnect(Postgres(), ...),
  tibble(col = 1:3),
  name = dbplyr::in_schema('test', 'name_test')
)

[krlmlr]

Ideally, we'd be able to unquote manually constructed qualified names:

library(DBI)
dbUnquoteIdentifier(ANSI(), SQL("schema.table"))
#> Error: Can't unquote schema.table
dbUnquoteIdentifier(ANSI(), SQL('"schema"."table"'))
#> [[1]]
#> <Id> schema = schema, table = table

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