plug (version 0.2.0)

plug_execute_query: Execute a custom SQL query on the Plug database

Description

This function executes a user-defined SQL query on the Plug database, with safe query construction using the same placeholder syntax as glue::glue_sql().

Usage

plug_execute_query(
  sql_template,
  endpoint = "https://plug.der.pe.gov.br/MadrixApi/executeQuery",
  verbosity = 0,
  ...,
  auth_endpoint = "https://plug.der.pe.gov.br/MadrixApi/authenticate/",
  .envir = parent.frame()
)

Value

A tibble containing the query results.

Arguments

sql_template

A SQL query template with placeholders for variables.

endpoint

The endpoint URL for executing queries.

verbosity

The verbosity level of the API request (0 = none, 1 = minimal, 2 = detailed).

...

Named arguments to replace placeholders in the SQL template.

auth_endpoint

The endpoint URL for generating the token.

.envir

The environment in which placeholders not supplied in ... are evaluated.

Details

Values are inserted in the query as Microsoft SQL Server literals:

• {x} inserts a quoted value: strings are wrapped in single quotes (with embedded quotes escaped), dates and date-times are formatted, logical values become 1/0 and NA becomes NULL.

• {x*} inserts all the elements of x separated by commas, for use with IN (...).

• {`x`} inserts a quoted identifier, such as a table or column name.

• {I(x)} inserts x as is, without any quoting.

Use double braces ({{ and }}) to insert literal braces in the query.

If the API rejects the cached token, a new token is generated and the query is executed again.

Examples

if (FALSE) {
data <- plug_execute_query(sql_template = "SELECT TOP 1 * FROM Contratos_VIEW")

# Using placeholders
data <- plug_execute_query(
  "SELECT TOP {n} * FROM {`base`} WHERE Ano IN ({years*})",
  n = 10,
  base = "Contratos_VIEW",
  years = c(2023, 2024)
)
}