如何将存储在r变量中的值传递给r中postgresql查询的where子句中的列

Question

我在RStudio中使用RPostgresql和DBI。

library(RPostgreSQL)
library(DBI)
#save password
prod_pw <- {
"my_pass"
}
# make db connection
con <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = 'my_dbname', 
host = 'my_host',
port = 5432, # or any other port
user = 'user_name',
password = prod_pw)

# save query
myquery<- 'select count(*), state from results where date='2018-11-10';'

#run query
my_query_stats<-dbGetQuery(con,myquery)

然而，我想自动化这一点，这样

日期可以由用户输入，也可以至少使用运行脚本时的系统日期。

我尝试的内容：例如：

this_date<-Sys.Date()
#or accept from user
this_date<- readline("Please Enter Daten")  
# Please Enter Date2018-11-30
# this_date
# [1] "2018-11-30"
myquery<- 'select count(*), state from results where date=this_date;'
dbGetQuery(con,myquery) # didn't work, null value returned.
myquery<- 'select count(*), state from results where date='this_date';'
dbGetQuery(con,myquery) # didn't work, null value returned.
myquery<- 'select count(*), state from results where date="this_date";'
dbGetQuery(con,myquery) # didn't work, returned null value.

请建议如何接受用户的值并将其发送到psql查询的日期字段。

Answer 1

尝试这个

this_date = "2018-11-30"

string = paste("select count(*), state from results where date= 
TO_DATE(this_date,'YYYYMMDD')")
rs = dbGetQuery(connection,string)