Insert R list into RPostgreSQL query

Question

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?

dave-edison · Accepted Answer · 2022-02-14 20:34:02Z

1

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)

answered Feb 14, 2022 at 20:34

dave-edison

3,7369 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

spencergadd Over a year ago

So forgive my poor sql background; my query is approximately 500 lines long and contains multple quotation marks, which, in this example, buggers things up. I tried saving the ({id*}) in my script and then script <- read_file("my_query.sql") query <- glue::glue_sql(script, .con = snappConnection) but had no luck. Any idea how to adjust such that my current script's quotation marks don't interfere?

spencergadd Over a year ago

I guess I should be more specific. After saving the script as a string with read_file(), and running

test.query <- glue::glue_sql(test.script, .con = snappConnection)  core.data <- dbGetQuery(conn = snappConnection, statement = test.query)

, I get the Error: Expecting a single string value: [type=character; extent=6]. I currently have 6 elements in my id list, fyi.

spencergadd Over a year ago

Nope, I just had a typo. It worked! Thank you.

spencergadd · Accepted Answer · 2022-02-14 22:00:58Z

0

@dave-edison's answer solved my problem. Concurrent to trying his, I got this to work.

I saved the query below as "my_query.sql"

SELECT *
FROM my_query
WHERE my_query.id_col IN ('string_to_replace')

then created a string and used gsub on the string.

library(tidyverse)
temp.script <-  read_file("my_query.sql")
core.data.script <- gsub('string_to_replace',paste0(id,collapse = "', '"),temp.script)

From there I just ran my RPostgres script like above.

answered Feb 14, 2022 at 22:00

spencergadd

577 bronze badges

Collectives™ on Stack Overflow

Insert R list into RPostgreSQL query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related