dbQuoteIdentifier() does not allow columns in Id() object.

[dpprdan]

dbQuoteIdentifier() currently does not allow for columns when quoting an Id() object.

library(DBI)

con <- dbConnect(RPostgres::Postgres(), dbname = "postgres")

id <- DBI::Id(table = "table_name", column = "column_name")

dbQuoteIdentifier(conn = con, x = id)
#> Error in dbQuoteIdentifier(conn = con, x = id): all(names(x@name) %in% c("catalog", "schema", "table")) is not TRUE

I think it should, and the glue_sql() docs seem to agree (the last example contains an implizit dbQuoteIdentifier() if I am not mistaken).

cols <- list(
  DBI::Id(table = "table_name", column = "col1"),
  DBI::Id(table = "table_name", column = "col2")
)

glue::glue_sql("SELECT {`cols`*}", .con = con)
#> Error in FUN(X[[i]], ...): all(names(x@name) %in% c("catalog", "schema", "table")) is not TRUE

dbDisconnect(con)

Would it suffice to add "column" here:

RPostgres/R/quote.R

Line 58 in 12c3d3d

stopifnot(all(names(x@name) %in% c("catalog", "schema", "table")))

and change

RPostgres/R/quote.R

Lines 61 to 71 in 12c3d3d

	ret <- ""
	if ("catalog" %in% names(x@name)) {
	ret <- paste0(ret, dbQuoteIdentifier(conn, x@name[["catalog"]]), ".")
	}
	if ("schema" %in% names(x@name)) {
	ret <- paste0(ret, dbQuoteIdentifier(conn, x@name[["schema"]]), ".")
	}
	if ("table" %in% names(x@name)) {
	ret <- paste0(ret, dbQuoteIdentifier(conn, x@name[["table"]]))
	}
	SQL(ret)

to

return(SQL(paste0(dbQuoteIdentifier(conn, x@name), collapse = ".")))

(from https://github.com/r-dbi/DBI/blob/6531ab38fc0d21745b1f40fdd836ba82f926c287/R/quote.R#L112)

Or am I missing something?

Session info

devtools::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value                       
#>  version  R version 4.0.2 (2020-06-22)
#>  os       Windows 10 x64              
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language en                          
#>  collate  German_Germany.1252         
#>  ctype    German_Germany.1252         
#>  tz       Europe/Berlin               
#>  date     2020-10-06                  
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version    date       lib source                             
#>  assertthat    0.2.1      2019-03-21 [1] CRAN (R 4.0.2)                     
#>  backports     1.1.10     2020-09-15 [1] CRAN (R 4.0.2)                     
#>  bit           4.0.4      2020-08-04 [1] CRAN (R 4.0.2)                     
#>  bit64         4.0.5      2020-08-30 [1] CRAN (R 4.0.2)                     
#>  blob          1.2.1      2020-01-20 [1] CRAN (R 4.0.2)                     
#>  callr         3.4.4      2020-09-07 [1] CRAN (R 4.0.2)                     
#>  cli           2.0.2      2020-02-28 [1] CRAN (R 4.0.2)                     
#>  crayon        1.3.4      2017-09-16 [1] CRAN (R 4.0.2)                     
#>  DBI         * 1.1.0      2019-12-15 [1] CRAN (R 4.0.2)                     
#>  desc          1.2.0      2018-05-01 [1] CRAN (R 4.0.2)                     
#>  devtools      2.3.2      2020-09-18 [1] CRAN (R 4.0.2)                     
#>  digest        0.6.25     2020-02-23 [1] CRAN (R 4.0.2)                     
#>  ellipsis      0.3.1      2020-05-15 [1] CRAN (R 4.0.2)                     
#>  evaluate      0.14       2019-05-28 [1] CRAN (R 4.0.2)                     
#>  fansi         0.4.1      2020-01-08 [1] CRAN (R 4.0.2)                     
#>  fs            1.5.0      2020-07-31 [1] CRAN (R 4.0.2)                     
#>  glue          1.4.2      2020-08-27 [1] CRAN (R 4.0.2)                     
#>  highr         0.8        2019-03-20 [1] CRAN (R 4.0.2)                     
#>  hms           0.5.3      2020-01-08 [1] CRAN (R 4.0.2)                     
#>  htmltools     0.5.0      2020-06-16 [1] CRAN (R 4.0.2)                     
#>  knitr         1.30       2020-09-22 [1] CRAN (R 4.0.2)                     
#>  magrittr      1.5.0.9000 2020-09-30 [1] Github (tidyverse/magrittr@0221e18)
#>  memoise       1.1.0      2017-04-21 [1] CRAN (R 4.0.2)                     
#>  pkgbuild      1.1.0      2020-07-13 [1] CRAN (R 4.0.2)                     
#>  pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.0.2)                     
#>  pkgload       1.1.0      2020-05-29 [1] CRAN (R 4.0.2)                     
#>  prettyunits   1.1.1      2020-01-24 [1] CRAN (R 4.0.2)                     
#>  processx      3.4.4      2020-09-03 [1] CRAN (R 4.0.2)                     
#>  ps            1.3.4      2020-08-11 [1] CRAN (R 4.0.2)                     
#>  R6            2.4.1      2019-11-12 [1] CRAN (R 4.0.2)                     
#>  Rcpp          1.0.5      2020-07-06 [1] CRAN (R 4.0.2)                     
#>  remotes       2.2.0      2020-07-21 [1] CRAN (R 4.0.2)                     
#>  rlang         0.4.7      2020-07-09 [1] CRAN (R 4.0.2)                     
#>  rmarkdown     2.4        2020-09-30 [1] CRAN (R 4.0.2)                     
#>  RPostgres     1.2.1.9000 2020-09-28 [1] local                              
#>  rprojroot     1.3-2      2018-01-03 [1] CRAN (R 4.0.2)                     
#>  sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 4.0.2)                     
#>  stringi       1.5.3      2020-09-09 [1] CRAN (R 4.0.2)                     
#>  stringr       1.4.0      2019-02-10 [1] CRAN (R 4.0.2)                     
#>  testthat      2.3.2      2020-03-02 [1] CRAN (R 4.0.2)                     
#>  usethis       1.6.3      2020-09-17 [1] CRAN (R 4.0.2)                     
#>  vctrs         0.3.4      2020-08-29 [1] CRAN (R 4.0.2)                     
#>  withr         2.3.0      2020-09-22 [1] CRAN (R 4.0.2)                     
#>  xfun          0.18       2020-09-29 [1] CRAN (R 4.0.2)                     
#>  yaml          2.2.1      2020-02-01 [1] CRAN (R 4.0.0)                     
#> 
#> [1] C:/Users/daniel/Documents/.R/win-library
#> [2] C:/Program Files/R/R-4.0.2/library

[dpprdan]

I missed dbUnquoteIdentifier() at least

    library(DBI)

    con <- dbConnect(RPostgres::Postgres(), dbname = "postgres")

    id <- DBI::Id(table = "table_name", column = "column_name")

    qid <- dbQuoteIdentifier(conn = con, x = id)

    dbUnquoteIdentifier(conn = con, x = qid)
    #> [[1]]
    #> <Id> schema = table_name, table = column_name

    dbDisconnect(con)

[krlmlr]

Is there any SQL dialect where another symbol than the dot is used as the delimiter between table (possibly fully qualified) and column name?

[dpprdan]

Not sure whether this question was directed at me, but FWIW, I am not aware of any, but I am not an expert.

How is this relevant for RPostgres? (Because here you'd only have to worry about the PostgreSQL dialect (plus derivatives like Redshift).)

[dpprdan]

The suggested changes in the first post are now here: master...dpprdan:fix/col_in_dbQuoteIdentifier (slightly updated for correct element order).

[krlmlr]

There is one problem here: what should dbUnquoteIdentifier(con, "foo.bar") return? If we allow quoting columns, dbUnquoteIdentifier() is no longer an inverse to dbQuoteIdentifier() . Would that ever be a problem, or is it sufficient that dbQuoteIdentifier() is an inverse to dbUnquoteIdentifier() but not the other way round?

Maybe we really need dbQuoteColumn() and dbUnquoteColumn() which would assume that the last identifier is a column and pass the rest to db*Identifier() ? A default implementation could live in DBI (that motivated my other question if the column separator is always the dot across backends).

[krlmlr]

I no longer really like the Id() interface. Let's keep its scope to table identifiers.

[dpprdan]

I no longer really like the Id() interface.

What's the alternative? Is there another way to make the glue_sql() example in the first post above work?

[krlmlr]

No good alternative indeed.

A better version of dbUnquoteIdentifier() would need an extra argument that identifies the object kind (schema, table, column) to which the object belongs. If we open dbQuoteIdentifier(), the roundtrip Id -> quote -> unquote is guaranteed only for table objects -- so be it.