2

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?

Forge
  • 1,587
  • 1
  • 15
  • 36
  • Question: Are you getting multiple blocks of data in the same final data frame, or is the code behaving as if the loop only iterates once, with the _last_ schema? – Tim Biegeleisen Oct 29 '18 at 07:27
  • I get printed every schema name but i only get as if the only schema visited is the last one. Once. – Forge Oct 29 '18 at 07:28
  • I speculate that the `search_path` parameter is being ignored, and some default schema is being used. Can you include `schema.list` in your question? Can you successfully connect to each of these schemas directly from the command line? – Tim Biegeleisen Oct 29 '18 at 07:32
  • Yes, I can connect to every schema – Forge Oct 29 '18 at 08:12

0 Answers0