In patelm9/pg13: pg13 Package

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

pg13

Functionalized rendering, reading, and building of PostgreSQL statements using R. Other functions from the DatabaseConnector package are migrated here with added conditional steps, such as warning the user that a resultset has 0 rows, verbosity such that it is interactively known what operations are taking place, or SQL rendering in the console before the statement is sent and/or queried for troubleshooting in a separate client.

Installation

Install the development version from GitHub with:

# install.packages("devtools")
devtools::install_github("meerapatelmd/pg13")

Example

pg13 provides functions that simplifies queries such as joins from the R console.

First, a connection is made to Postgres. Here, I am connecting to my test database pg13_test.

library(pg13)
conn <- local_connect(dbname = "pg13_test")

create_test_schema <-
        function(conn) {

                if (!schema_exists(conn = conn,
                                   schema = "test_schema")) {

                        create_schema(conn = conn,
                                      schema = "test_schema")

                }
        }
create_test_schema(conn = conn)

# Write a table to join to without dropping
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table2",
            drop_existing = TRUE,
            data = data.frame(A = 1:25, B = letters[1:25]))

I write a target table in the test database with sample data:

test_table <- data.frame(A = 1:25, B = letters[1:25])
head(test_table)

The data is written to the target schema and table_name or "test_schema" and "test_table2" respectively. Messages appear in the console along with timestamp that elaborate on the connection status, the data dimensions, and the passage of QA checks.

write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table2",
            drop_existing = TRUE,
            data = data.frame(A = 1:25, B = letters[1:25]))

I create another dataframe in the R environment to serve as left-side table for this demonstration.

test_data <-
        data.frame(A = 1:100, B = letters[1:100])
head(test_data)

I can then perform various joins between this dataframe and the previously written table, such as an inner join

join1(conn = conn,
      write_schema = "public",
      data = test_data,
      column = "A",
      kind = "INNER",
      join_on_schema = "test_schema",
      join_on_table = "test_table2",
      join_on_column = "A")

a right join

join1(conn = conn,
      write_schema = "public",
      data = test_data,
      column = "A",
      kind = "RIGHT",
      join_on_schema = "test_schema",
      join_on_table = "test_table2",
      join_on_column = "A")

a left join

join1(conn = conn,
      write_schema = "public",
      data = test_data,
      column = "A",
      kind = "LEFT",
      join_on_schema = "test_schema",
      join_on_table = "test_table2",
      join_on_column = "A")

or a full join:

join1(conn = conn,
      write_schema = "public",
      data = test_data,
      column = "A",
      kind = "FULL",
      join_on_schema = "test_schema",
      join_on_table = "test_table2",
      join_on_column = "A")

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

dc(conn = conn)

Code of Conduct

Please note that the pg13 project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

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