Drop a Batch of Tables using Regex

drop_table_batch(
  conn,
  conn_fun,
  schema,
  table,
  if_exists = TRUE,
  pattern,
  ignore.case = FALSE,
  perl = FALSE,
  fixed = FALSE,
  useBytes = FALSE,
  invert = FALSE,
  verbose = TRUE,
  render_sql = TRUE,
  render_only = FALSE,
  ...
)

Arguments

conn	Connection object
schema	The target schema for the operation.
table	Target table for the operation.
if_exists	If TRUE, the table will be dropped only if it exists.
pattern	character string containing a regular expression (or character string for `fixed = TRUE`) to be matched in the given character vector. Coerced by `as.character` to a character string if possible. If a character vector of length 2 or more is supplied, the first element is used with a warning. Missing values are allowed except for `regexpr` and `gregexpr`.
ignore.case	if `FALSE`, the pattern matching is case sensitive and if `TRUE`, case is ignored during matching.
perl	logical. Should Perl-compatible regexps be used?
fixed	logical. If `TRUE`, `pattern` is a string to be matched as is. Overrides all conflicting arguments.
useBytes	logical. If `TRUE` the matching is done byte-by-byte rather than character-by-character. See ‘Details’.
invert	logical. If `TRUE` return indices or values for elements that do not match.
verbose	If TRUE, details on the activity are returned in the console, such as when the querying starting and was completed.
render_sql	If TRUE, the SQL statement for the operation is returned in the console.
...	Additional arguments passed to `DatabaseConnector::dbSendStatement()`

Examples

library(pg13)
create_test_schema <-
        function(conn) {

                if (!schema_exists(conn = conn,
                                   schema = "test_schema")) {

                        create_schema(conn = conn,
                                      schema = "test_schema")

                }
        }

conn <- local_connect(dbname = "pg13_test")
#> Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect",     as.character(url), p): org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
create_test_schema(conn = conn)
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbGetQuery': object 'conn' not found

# Write a table without dropping
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table2",
            drop_existing = FALSE,
            data = data.frame(A = 1:3, B = letters[1:3]))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Write a table with dropping
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table",
            drop_existing = TRUE,
            data = data.frame(A = 1:3, B = letters[1:3]))
#> [2021-06-20 15:26:11]	Dropping test_schema.test_table...
#> Error in check_conn_status(conn = conn): object 'conn' not found


# Include the name of the dataframe object in the messages
test_data <-
        data.frame(A = 1:3, B = letters[1:3])
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table3",
            drop_existing = TRUE,
            data = test_data)
#> [2021-06-20 15:26:11]	Dropping test_schema.test_table3...
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Append a table
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = data.frame(A = 1:3, B = letters[1:3]))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Warning is returned if NAs are in the input data
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = data.frame(A = 1:3, B = rep(NA_character_, 3)))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Alert is returned if the input data contains 0 rows
test_data2 <-
        data.frame(A = 1,
                   B = 2)
test_data2 <- test_data2[-1,]
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = test_data2)
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Message is returned if incoming data contains more than 0 rows
test_data <- read_table(conn = conn,
                           schema = "test_schema",
                           table = "test_table")
#> Error in read_table(conn = conn, schema = "test_schema", table = "test_table"): unused arguments (conn = conn, schema = "test_schema", table = "test_table")

test_data
#>   A B
#> 1 1 a
#> 2 2 b
#> 3 3 c

# Alert is returned if incoming data contains 0 rows
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table4",
            drop_existing = TRUE,
            data = test_data2)
#> [2021-06-20 15:26:11]	Dropping test_schema.test_table4...
#> Error in check_conn_status(conn = conn): object 'conn' not found
test_data <- read_table(conn = conn,
                        schema = "test_schema",
                        table = "test_table4")
#> Error in read_table(conn = conn, schema = "test_schema", table = "test_table4"): unused arguments (conn = conn, schema = "test_schema", table = "test_table4")
test_data
#>   A B
#> 1 1 a
#> 2 2 b
#> 3 3 c

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = 1:3)
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = 1:3): could not find function "search_table"

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = "a")
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = "a"): could not find function "search_table"

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = c("A", "b", "C"),
             case_insensitive = FALSE)
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = c("A", "b", "C"), case_insensitive = FALSE): could not find function "search_table"

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table",
           if_exists = FALSE)
#> [2021-06-20 15:26:11]	Dropping test_schema.test_table...
#> Error in check_conn_status(conn = conn): object 'conn' not found

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table2",
           if_exists = FALSE)
#> [2021-06-20 15:26:11]	Dropping test_schema.test_table2...
#> Error in check_conn_status(conn = conn): object 'conn' not found

drop_schema(conn = conn,
            schema = "test_schema",
            cascade = TRUE)
#> Error in check_conn_status(conn = conn): object 'conn' not found

dc(conn = conn)
#> [2021-06-20 15:26:11]	Postgres connection was already closed

Drop a Batch of Tables using Regex

Arguments

See also

Examples