Drop V Tables

Drop tables written by functions in this package that follows the Staging Table naming convention of "V" followed by 14 integers representing the timestamp of the transaction. This function will clear any of the tables that strictly follows this pattern. An expiration period can optionally be applied where the date and time of the transaction is parsed from the table name and will be dropped only if the difference between the system time and timestamp in the table name is greater than the time_diff_hours argument.

drop_all_staging_tables(
  conn,
  conn_fun,
  schema,
  time_diff_hours = 0,
  verbose = TRUE,
  render_sql = TRUE
)

Arguments

time_diff_hours	Numeric designating the period of time in hours after which the the table should be considered expired. If 0, all tables will be dropped.

Value

If an expiration period is provided with a time_diff_hours greater than 0, a console message of the names of any tables following this convention remain in schema. If time_diff_hours is 0, all tables are dropped.

See also

Other staging table functions: write_staging_table()

Other table functions: appendTable(), append_table(), create_table_from_df(), create_table(), drop_table_batch(), drop_table(), read_table(), searchTable(), search_table(), write_staging_table(), write_table()

Other drop functions: drop_cascade(), drop_if_exists(), drop_schema(), drop_table_batch(), drop_table()

Examples

library(pg13)
library(tidyverse)
test_data <-
        tibble::tibble(A = 1:3,
                       B = letters[1:3],
                       C = c(TRUE, FALSE, TRUE),
                       D = c(1.23421, 234.23421, 342.0134014134),
                       E = c(Sys.Date(), Sys.Date()-1013, Sys.Date() + 134))
conn <- local_connect("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.


# Writing a staging table. Note that since the function is not called
# inside a enclosed parent frame, the table is not dropped even when
# drop_on_exit is set to TRUE
write_staging_table(conn = conn,
                    schema = "public",
                    data = test_data,
                    drop_on_exit = TRUE)
#> Error in check_conn_status(conn = conn): object 'conn' not found

ls_tables(conn = conn,
          schema = "public")
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found


# All tables in a schema can be dropped with pattern matching to the "V{timestamp}" format
drop_all_staging_tables(conn = conn,
                        schema = "public")
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found

# The `time_diff_hours` option allows for the user to drop all tables other than the most recently written tables based on the number of hours from the current timestamp.
write_staging_table(conn = conn,
                    schema = "public",
                    data = test_data)
#> Error in check_conn_status(conn = conn): object 'conn' not found

drop_all_staging_tables(conn = conn,
                        schema = "public",
                        time_diff_hours = 8)
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found

ls_tables(conn = conn,
          schema = "public")
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found

# If this function is called within an enclosed parent frame with `drop_on_exit` set to TRUE, the table will be dropped at the conclusion of the function.
test_fun <-
        function() {

                new_table <-
                write_staging_table(conn = conn,
                                    schema = "public",
                                    data = test_data,
                                    drop_on_exit = TRUE)

                print("The test is finished.")
        }

test_fun()
#> Error in check_conn_status(conn = conn): object 'conn' not found
ls_tables(conn = conn,
          schema = "public")
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found

# Dropping all staging tables before exiting the example
drop_all_staging_tables(conn = conn,
                        schema = "public",
                        time_diff_hours = 8)
#> [2021-06-20 15:26:09]	SQL: N/A
#> [2021-06-20 15:26:09]	Listing Tables...
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbListTables': object 'conn' not found

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