pg: pg* wrappers to DBI::db*

In jangorecki/pg: Postgres database tools for R

Description

Many wrappers around 'DBI::db*' functions. Some new wrappers added are 'pgTruncateTable', 'pgExistsSchema', 'pgDropSchema', 'pgListTableColumns'. Follow 'tests/tests.R' script for reproducible workflow with tests.

Usage

pgConnect(host = Sys.getenv("POSTGRES_HOST", "127.0.0.1"),
  port = Sys.getenv("POSTGRES_PORT", "5432"),
  dbname = Sys.getenv("POSTGRES_DB", "postgres"),
  user = Sys.getenv("POSTGRES_USER", "postgres"),
  password = Sys.getenv("POSTGRES_PASSWORD", "postgres"))

pgGetVersion(conn = getOption("pg.conn"), .log = getOption("pg.log", TRUE))

pgSendQuery(statement, silent = FALSE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgGetQuery(statement, key, norows, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgWriteTable(name, value, techstamp = TRUE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgReadTable(name, key, norows, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgExistsTable(name, conn = getOption("pg.conn"), .log = getOption("pg.log",
  TRUE))

pgExistsSchema(schema_name, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgListTables(schema_name, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgTruncateTable(name, silent = FALSE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgCreateSchema(schema_name, silent = FALSE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgListSchema()

pgListFields(name, conn = getOption("pg.conn"), .log = getOption("pg.log",
  TRUE))

pgListTableColumns(schema_name, select = c("table_schema", "table_name",
  "column_name", "ordinal_position", "column_default", "is_nullable",
  "data_type", "character_maximum_length", "numeric_precision",
  "datetime_precision"), conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgDropSchema(schema_name, cascade = FALSE, silent = FALSE,
  conn = getOption("pg.conn"), .log = getOption("pg.log", TRUE))

pgRemoveTable(name, cascade = FALSE, silent = FALSE,
  conn = getOption("pg.conn"), .log = getOption("pg.log", TRUE))

pgDropTable(name, cascade = FALSE, silent = FALSE,
  conn = getOption("pg.conn"), .log = getOption("pg.log", TRUE))

pgSendUpsert(stage_name, name, conflict_by, on_conflict = "DO NOTHING",
  techstamp = TRUE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgUpsertTable(name, value, conflict_by, on_conflict = "DO NOTHING",
  stage_name, techstamp = TRUE, conn = getOption("pg.conn"),
  .log = getOption("pg.log", TRUE))

pgDisconnect(conn = getOption("pg.conn"))

pgExplain(statement, conn = getOption("pg.conn"), .log = getOption("pg.log",
  TRUE))

Arguments

host	character hostname/ip by default from ENV var 'POSTGRES_HOST'
port	character port by default from ENV var 'POSTGRES_PORT'
dbname	character port by default from ENV var 'POSTGRES_DB'
user	character port by default from ENV var 'POSTGRES_USER'
password	character port by default from ENV var 'POSTGRES_PASSWORD'
conn	active connection to postgres database, by default 'getOption("pg.conn")'.
.log	logical default 'getOption("pg.log",TRUE)' decides if call is logged using *logR*.
statement	character scalar
silent	logical catch potential errors, useful for potentially non existing ojbect on while dropping.
key	character vector of columns to be used to set data.table key on the db results.
norows	arbitrat object which will be returned in case of 0 rows result from db.
name	character
value	data.table
techstamp	logical decides if 'dbWriteTable' will add technical metadata on saving each object to db.
schema_name	character vector of schema names to be checked by 'pgExistsSchema' or dropped by 'pgDropSchema'.
select	character vector of column names to fetch from 'information_schema.columns' table.
cascade	logical use cascade drop while dropping object.
stage_name	character, staging schema-table name used for performing *Upsert*.
conflict_by	character vector, will be used collapsed in 'ON CONFLICT (conflict_by) DO ...'.
on_conflict	character scalar to be send as 'ON CONFLICT' postgres content. Key column set for conflict may be included here, but then should not be provided to 'conflict_by' argument. Default 'DO NOTHING'.

Details

1. Instead of reusing 'conn' arg in each query just save it to option 'options("pg.conn" = pgConnect())' and it will be reused. 2. Logging is by default enabled, table for logs needs to be created, use 'logR::logR_schema()' potentially with custom defined metadata columns. See 'tests/tests.R' or *logR* package documentation. 3. Stamping data by technical run id requires another table which can be created using 'create_run_table()'. This feature has to be utilized as logR custom metadata values, see 'tests/tests.R'.

jangorecki/pg documentation built on May 18, 2019, 12:24 p.m.