write_staging_table: Write a Staging Table.

In patelm9/pg13: pg13 Package

Description

A Staging Table is one that is named automatically in a naming convention of "V" followed by 14 integers representing the timestamp of the transaction. The staging table can optionally be dropped on exit in the parent frame from which the function is being called when drop_on_exit is set to TRUE.

Usage

write_staging_table(
  conn,
  conn_fun,
  schema,
  data,
  drop_existing = FALSE,
  drop_on_exit = FALSE,
  verbose = TRUE,
  render_sql = TRUE,
  ...
)

Arguments

conn	Connection object
schema	The target schema for the operation.
data	A dataframe or tibble.
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.

Value

String of the staging table

See Also

Other staging table functions: drop_all_staging_tables()

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

Other write functions: append_table(), write_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")


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

patelm9/pg13 documentation built on Dec. 26, 2021, 8:17 p.m.