List tables in schema with dbListTables

[ablack3]

I would like to list tables in a specific schema using dbListTables and use syntax that will work with other DBI backends that support listing tables in a schema (e.g. odbc).

library(DBI)
con <- DBI::dbConnect(RPostgres::Postgres(),
                      host     = "localhost",
                      dbname   = "cdm",
                      user     = "postgres",
                      password = "",
                      port     = 5432)

dbIsValid(con)
#> [1] TRUE

dbExecute(con, "create schema test;")
#> [1] 0
dbWriteTable(con, Id(schema = "test", table = "iris"), iris)
dbListTables(con)
#> character(0)
dbListTables(con, schema_name = "test")
#> character(0)

dbExecute(con, "SET search_path TO test")
#> [1] 0
dbListTables(con)
#> [1] "iris"


dbExecute(con, "drop schema test cascade")
#> NOTICE:  drop cascades to table iris
#> [1] 0
dbDisconnect(con)

^{Created on 2022-07-08 by the reprex package (v2.0.1)}

RPostgres/R/dbListTables_PqConnection.R

Line 3 in 384bd3f

dbListTables_PqConnection <- function(conn, ...) {

[plpxsk]

Here is one way

In DBI::dbConnect(), pass the following option:

options = "-c search_path=myschema"

Or, to specify schema elsewhere:

options = paste0("-c search_path=", schema)

Then, listing and writing tables should work directly on this connection

Source: https://stackoverflow.com/a/49110504/3217870

EDIT: minor edits

[plpxsk]

An alternative is to list the "objects" (tables) in the schema:

dbListObjects(con, DBI::Id(schema = schema))

Via https://stackoverflow.com/a/66717542/3217870

[krlmlr]

Thanks.

The options argument is documented in https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS which is linked from https://rpostgres.r-dbi.org/reference/postgres#arguments-1. It's very powerful indeed.

How can we improve the documentation to make this information easier to access?

library(DBI)
con <- DBI::dbConnect(RPostgres::Postgres())

dbExecute(con, "create schema if not exists test;")
#> [1] 0
dbWriteTable(con, Id(schema = "test", table = "iris"), iris, overwrite = TRUE)
dbListObjects(con, DBI::Id(schema = "test"))
#>                              table is_prefix
#> 1 <Id> schema = test, table = iris     FALSE

con2 <- DBI::dbConnect(RPostgres::Postgres(), options = "-c search_path=test")
dbListTables(con2)
#> [1] "iris"
dbDisconnect(con2)


dbExecute(con, "drop schema test cascade")
#> NOTICE:  drop cascades to table test.iris
#> [1] 0
dbDisconnect(con)

^{Created on 2023-04-01 with reprex v2.0.2}

[SpikyClip]

Just chiming in to say that this feature would be useful. I support some non-technical users who use dbplyr to access a database which is segregated by schema, and both these existing solutions are just a bit too complicated. My only neat workaround is to create a bunch of views in one schema just for them.