Fail to detect dollar-quoted string constants in postgresql database

[ZhangAngus]

Checklist

When filing a bug report, please check the boxes below to confirm that you have provided us with the information we need. Have you:

• [x] formatted your issue so it is easier for us to read?

• [x] included a minimal, self-contained, and reproducible example?

• [x] pasted the output from xfun::session_info('rmarkdown') in your issue?

• [x] upgraded all your packages to their latest versions (including your versions of R, the RStudio IDE, and relevant R packages)?

• [ ] installed and tested your bug with the development version of the rmarkdown package using remotes::install_github("rstudio/rmarkdown")?

1. Issue detail

---

Rmarkdown SQL chunk fail to detect dollar-quoted string constants feature in postgresql database.

According to the doc from postgres website(https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS), postgres has a feature to facilitate reading strings with escaped strings in session 4.1.2.4. Dollar-Quoted String Constants.

The sql query example works well in R script, but returns error in R SQL chunk.

2. Reproducible example

---

R script version:

library(DBI)
mydb <-
  dbConnect(
    RPostgres::Postgres(),
    password = "postgres",
    user = "postgres",
    dbname = "postgres",
    host = "localhost",
    port = 5432
  )

dbGetQuery(mydb, "SELECT $$Dianne's horse$$;")

Console returns:

	?column?
1	Dianne's horse

Markdown script version:

• R chunk

```{r}
library(DBI)
mydb <-
  dbConnect(
    RPostgres::Postgres(),
    password = "postgres",
    user = "postgres",
    dbname = "postgres",
    host = "localhost",
    port = 5432
  )

• SQL chunk

```{sql, connection=mydb}
SELECT $$Dianne's horse$$;

Error in sqlParseVariablesImpl(sql, list(sqlQuoteSpec("\"", "\""), sqlQuoteSpec("'",  : 
  Unterminated literal
Failed to execute SQL chunk

3. xfun::session_info('rmarkdown')

---

R version 4.1.3 (2022-03-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 22000), RStudio 2021.9.2.382

Locale:
  LC_COLLATE=Chinese (Simplified)_China.936  LC_CTYPE=Chinese (Simplified)_China.936   
  LC_MONETARY=Chinese (Simplified)_China.936 LC_NUMERIC=C                              
  LC_TIME=Chinese (Simplified)_China.936    
system code page: 65001

Package version:
  bit_4.0.4       bit64_4.0.5     blob_1.2.2      cli_3.2.0       compiler_4.1.3  cpp11_0.4.2    
  DBI_1.1.2       ellipsis_0.3.2  evaluate_0.15   fortunes_1.5-4  generics_0.1.2  glue_1.6.2     
  graphics_4.1.3  grDevices_4.1.3 highr_0.9       hms_1.1.1       knitr_1.38      lifecycle_1.0.1
  lubridate_1.8.0 magrittr_2.0.3  methods_4.1.3   pkgconfig_2.0.3 plogr_0.2.0     Rcpp_1.0.8.3   
  rlang_1.0.2     RPostgres_1.4.3 stats_4.1.3     stringi_1.7.6   stringr_1.4.0   tools_4.1.3    
  utils_4.1.3     vctrs_0.4.0     withr_2.5.0     xfun_0.30       yaml_2.3.5     

Since the R script version worked fine, so I guess the key may lays in rmarkdown.

[cderv]

Thanks for the report. I'll have a look. This could happen somewhere in knitr eng_sql() engine from where we retrieve the code chunk content to pass it to DBI::dbSendQuery().