drop_all_staging_tables: Drop V Tables
In patelm9/pg13: pg13 Package

Description Usage Arguments Value See Also Examples

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
)

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.

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.

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()

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")


# 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)

ls_tables(conn = conn,
          schema = "public")


# All tables in a schema can be dropped with pattern matching to the "V{timestamp}" format
drop_all_staging_tables(conn = conn,
                        schema = "public")

# 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)

drop_all_staging_tables(conn = conn,
                        schema = "public",
                        time_diff_hours = 8)

ls_tables(conn = conn,
          schema = "public")

# 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()
ls_tables(conn = conn,
          schema = "public")

# Dropping all staging tables before exiting the example
drop_all_staging_tables(conn = conn,
                        schema = "public",
                        time_diff_hours = 8)

dc(conn = conn)