I have a bunch of identical schemas in PostgreSQL I want to get data from. I use RpostgreSQL inside a loop this way
``` r
results <- data.frame()
for (schema in schema.list ) {
con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
user = 'user',
password = 'pwd',
dbname = 'mydb',
host = 'myhost.com',
options = paste0(" -c search_path=", schema)
)
tbl <- dplyr::tbl(con, sql(my_sql_query)) %>% collect()
results <- rbind(results,tbl)
dbDisconnect(con)
}
```
The loop is working but I only get the last schema data. Once. As if it gets inside the loop and erased all data but the last one.
How can be sure of setting the search path when queryng this way?