query: Execute Dynamic PostgreSQL Queries

In joshua-ruf/fidelis: Automate Data Reporting

Description

The query() function sends a query with dynamic inputs to a PostgreSQL database, allowing loops and automated reporting. The madlib() function just prints the query to console after replacing dynamic inputs, useful for debugging.

Usage

query(query, ...)

madlib(query, ...)

Arguments

query	A character string with dynamic inputs surrounded by "%" (i.e. %<variable>%)
...	A named series of objects to insert into the query, these CAN be reused. Objects of length 1 will be left as is, whereas objects of length > 1 will be surrounded with brackets. Objects of type character will be surrounded by single quotations and objects of type numeric will be left as is.

Note

• Ensure there is a connection obejct 'conn' in your environment;

• Do not comment with --comment, instead use /*comment*/.

Examples

query(query = "select * from table where place in %places% and year in %years%;",
       places = c('NYC', 'Vancouver', 'Hamilton'),
       years = c(2019, 2018, 2017))


query <- "
select *
from table
where place in %places%
and year in %years%
;
"
places <- c('NYC', 'Vancouver', 'Hamilton')
years <- c(2019, 2018, 2017)

query(query = query,
       places = places,
       years = years)

madlib(query = "select * from table where place in %places% and year in %years%;",
       places = c('NYC', 'Vancouver', 'Hamilton'),
       years = c(2019, 2018, 2017)) # returns a character object

joshua-ruf/fidelis documentation built on July 20, 2019, 1:56 a.m.