Build a Simple SQL Query

build_query(
  fields = "*",
  distinct = FALSE,
  schema,
  table,
  where_in_field,
  where_in_vector,
  where_not_in_field,
  where_not_in_vector,
  where_is_null_field,
  where_is_not_null_field,
  case_insensitive = TRUE,
  limit,
  random
)

Arguments

fields	Fields selected for. Defaults to "*".
distinct	If TRUE, the distinct row count will be returned.
schema	The target schema for the operation.
table	Target table for the operation.
where_in_field	Paired with `where_in_vector`, adds a "WHERE field IN (vector)" clause to the query corresponding to the field-value pair.
where_not_in_field	Paired with `where_not_in_vector`, adds a "WHERE field NOT IN (vector)" clause to the query corresponding to the field-value pair.
case_insensitive	If TRUE, both sides of the query are converted to lowercase.
limit	(Optional) Integer of the row limit. Takes precedence over `random` if both arguments are provided.
random	(Optional) Integer of the random number of rows to return. Is preceded by `limit` if both `limit` and `random` are provided.

Examples

# Building SQL Statements using R
library(pg13)

# Build a Simple Query
build_query(schema = "public",
            table = "concept_synonym")
#> [1] "SELECT *\nFROM public.concept_synonym"

build_query(distinct = TRUE,
            schema = "public",
            table = "concept_synonym")
#> [1] "SELECT DISTINCT *\nFROM public.concept_synonym\n"


build_query(fields = c("concept_id", "concept_name"),
            distinct = TRUE,
            schema = "public",
            table = "concept_synonym")
#> [1] "SELECT DISTINCT concept_id,concept_name\nFROM public.concept_synonym\n"

# WHERE filters may be applied, but will be ignored if the field-value pair is not provided
build_query(fields = c("concept_id", "concept_name"),
            schema = "public",
            table = "concept_synonym",
            where_in_field = "concept_synonym")
#> Error in typewrite_alert_danger("both `where_in_field` & `where_in_vector` required. Ignoring filter...",     wrap = TRUE): unused argument (wrap = TRUE)

# The query defaults to transforming elements to lowercase for case insensitivity that can be turned off
build_query(fields = c("concept_id", "concept_name"),
            schema = "public",
            table = "concept_synonym",
            where_in_field = "concept_synonym",
            where_in_vector = c("Myocardial Infarction", "Heart Attack"))
#> [1] "SELECT concept_id,concept_name\nFROM public.concept_synonym\nWHERE\nLOWER(a.concept_synonym::varchar) IN ('myocardial infarction','heart attack')"

build_query(fields = c("concept_id", "concept_name"),
            schema = "public",
            table = "concept_synonym",
            where_in_field = "concept_synonym",
            where_in_vector = c("Myocardial Infarction", "Heart Attack"),
            case_insensitive = FALSE)
#> [1] "SELECT concept_id,concept_name\nFROM public.concept_synonym\nWHERE\na.concept_synonym IN ('Myocardial Infarction','Heart Attack')"

Build a Simple SQL Query

Arguments

See also

Examples