[SOLVED] Insert R list into RPostgreSQL query

Insert R list into RPostgreSQL query

I'm running a postgreSQL query based on an automated list of ID's stored in an R list. I'm trying to determine how to include that R list in my query so I don't have to hard-code the ID's each time I run my query.

For example, I have a script that produces the list

id <- c("001","002","003")

and my query looks something like this:

SELECT *
FROM my_query
WHERE my_query.id_col IN ('001', '002', '003')

which I run using Rpostgres:

library(Rpostgres)
snappConnection <- DBI::dbConnect(RPostgres::Postgres(),
                                  host = "host",
                                  dbname = "dbname",
                                  user = "user",
                                  password = "pword",
                                  port = 0000)
core.data <- dbGetQuery(conn = snappConnection,statement = SELECT * FROM my_query WHERE my_query.id_col IN ('001', '002', '003'))

Is there a way to reference my "id" list from R in my query so that when "id" updates to new values, the query also updates to those new values?

Solution

glue_sql from glue package should work:

query <- glue::glue_sql("
SELECT *
FROM my_query
WHERE my_query.id_col IN ({id*})              
", .con = snappConnection)

core.data <- dbGetQuery(conn = snappConnection, statement = query)