Write a Staging Table.

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.

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")
#> 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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	SQL: N/A
#> [2021-06-20 15:26:30]	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:30]	Postgres connection was already closed