UPDATE {pgTools}

Generate a PostgreSQL UPDATE statement, optionally execute the statement if con is not NULL.

Description

Generate a PostgreSQL UPDATE statement, optionally execute the statement if con is not NULL.

Usage

UPDATE(
  x,
  schema = NULL,
  table,
  where = list(),
  prepare = TRUE,
  types = NULL,
  returning = NULL,
  quote_text = TRUE,
  cast = TRUE,
  con = NULL
)

Arguments

x

A named list, names must match the column names of the destination SQL table, values are the values to set the SQL records to.

schema

A string, the schema name of the destination SQL table.

table

A string, the table name of the destination SQL table.

where

A named list, names are the columns for comparison, values are lists with a comparison operator and a value the comparison operator will check against. ex: list(col1 = list(comparison = "=", value = quoteText("b")))

prepare

TRUE/FALSE, if TRUE, creates a PostgreSQL prepared statement for inserting the data.

types

A vector of character strings specifying the SQL data types of the destination columns, the position of the type should match the position of the column for that type in x. Required if prepare or cast is TRUE.

returning

A vector of character strings specifying the SQL column names to be returned by the INSERT statement.

quote_text

TRUE/FALSE, if TRUE, calls quoteText() to add single quotes around character strings.

cast

TRUE/FALSE, if TRUE, will add SQL to cast the data to be inserted to the specified type.

con

A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery.

Value

A string, PostgreSQL UPDATE statement; or a string, PostgreSQL prepared statement; or the results retrieved by DBI::dbGetQuery after executing the statement.

Examples

UPDATE(
x = list(col1 = "a", col2 = 1),
schema = "test",
table = "table1",
where = list(
  col1 = list(comparison = "=", value = quoteText("b")),
  col2 = list(comparison = "IS", value = "NULL")
),
prepare = FALSE,
types = c("TEXT", "INTEGER"),
returning = c("col3"),
quote_text = TRUE,
cast = TRUE
)