search_table: Search a Table for a Value

In patelm9/pg13: pg13 Package

Description

Loop a query for a set of one or more values in a table across all the existing fields or optionally, a subset of the fields. Both the values and the table fields are ensured compatibility by 1. Converting each value in the values argument to the character class and 2. Casting each table field as varchar in the query.

Usage

search_table(
  conn,
  conn_fun,
  schema,
  table,
  ...,
  values,
  case_insensitive = TRUE,
  verbose = TRUE,
  render_sql = TRUE
)

Arguments

conn	Connection object
schema	The target schema for the operation.
table	Target table for the operation.
...	(Optional) Character strings of 1 or more fields in the table to search in.
values	Vector of length 1 or greater to search for.
case_insensitive	If TRUE, both sides of the query are converted to lowercase.
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.

See Also

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

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")
create_test_schema(conn = conn)

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

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


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

# Append a table
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = data.frame(A = 1:3, B = letters[1:3]))

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

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

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

test_data

# 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)
test_data <- read_table(conn = conn,
                        schema = "test_schema",
                        table = "test_table4")
test_data

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = 1:3)

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = "a")

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = c("A", "b", "C"),
             case_insensitive = FALSE)

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table",
           if_exists = FALSE)

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table2",
           if_exists = FALSE)

drop_schema(conn = conn,
            schema = "test_schema",
            cascade = TRUE)

dc(conn = conn)

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