Incorrect quoting of fully qualified table name by dbQuoteIdentifier

[Dmitrii-I]

A typical database will have multiple schemas. Not everything resides in public schema.

When using dbReadTable and providing fully qualified table name because the schema is not on the search path, the function dbQuoteIdentifier returns "schema.table" instead of "schema"."table". This leads to dbReadTable failing:

d <- dbReadTable(conn, 'my_schema.my_table')
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  relation "my_schema.my_table" does not exist
LINE 1: SELECT * from "my_schema.my_table"
                      ^
)
Error in names(out) <- make.names(names(out), unique = TRUE) : 
  attempt to set an attribute on NULL
In addition: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: SELECT * from "my_schema.my_table"

The documentation of dbQuoteIdentifier mentions that this function is used for column names. Note that dots in column names are treated differently than dots in table names! Perhaps don't use dbQuoteIdentifier for table names or dbReadTable should first split the table name on dots, then quote the parts, and return the parts pasted together.

[tomoakin]

Please try dbReadTable(conn, c('my_schema','my_table')), if you want to specify the schema.

RPostgreSQL have not come up with the latest DBI spec. and dbQuoteIdentifier is not used here, in fact. Because a table name containing a period "." is valid, schema.table shall not be interpreted as "schema"."table".

Also, note dbReadTable doc of DBI specify that the string should be processed with dbQuoteIdentifier, effectively making the same behavior.

[Dmitrii-I]

Thanks, will do.