postgresql@4.1.11

Functions

describeTable(tableName, [options], callback)

findValue([filter])

insert(table, record, [options], callback)

insertMany(table, records, [options], callback)

insertTable(tableName, columns, [options], callback)

modifyTable(tableName, columns, [options], callback)

sql(sqlQuery, [options], callback)

upsert(table, uuid, record, [options], callback)

upsertIf(logical, table, uuid, record, [options], callback)

upsertMany(table, uuid, data, [options], callback)

The following functions are exported from the common adaptor:

alterState()

arrayToString()

combine()

dataPath()

dataValue()

dateFns()

each()

field()

fields()

fn()

http()

lastReferenceValue()

merge()

sourceValue()

describeTable

describeTable(tableName, [options], callback) ⇒ Operation

List the columns of a table in a database.

Param	Type	Description
tableName	string	The name of the table to describe
[options]	object	Optional options argument
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

describeTable('clinic_visits')

---

findValue

findValue([filter]) ⇒ value

Fetch a uuid key given a condition

Param	Type	Description
[filter]	object	A filter object with the lookup table, a uuid and the condition
[filter.uuid]	string	The uuid value to search for in the specified relation.
[filter.relation]	string	The name of the relation to search for the uuid value.
[filter.where]	object	An object that contains key-value pairs to filter the search results.
[filter.operator]	object	An object that contains key-value pairs to specify the type of comparison to perform on the where clause.

Example

findValue({
   uuid: 'id',
   relation: 'users',
   where: { first_name: 'Mamadou' },
   operator: { first_name: 'like' }
 })

---

insert

insert(table, record, [options], callback) ⇒ Operation

Insert a record

Param	Type	Description
table	string	The target table
record	object	Payload data for the record as a JS object or function
[options]	object	Optional options argument
[options.setNull]	string	A string value that specifies the behavior for inserting null values.
[options.logValues]	boolean	A boolean value that specifies whether to log the inserted values to the console. Defaults to false.
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

insert('users', { name: 'Elodie', id: 7 }, { setNull: "'NaN'", logValues: true });

---

insertMany

insertMany(table, records, [options], callback) ⇒ Operation

Insert many records, using the keys of the first as the column template

Param	Type	Description
table	string	The target table
records	array	An array or a function that takes state and returns an array
[options]	object	Optional options argument
[options.setNull]	string	A string value that specifies the behavior for inserting null values.
[options.logValues]	boolean	A boolean value that specifies whether to log the inserted values to the console. Defaults to false.
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

insertMany('users', state => state.data.recordArray, { setNull: "'undefined'", logValues: true });

---

insertTable

insertTable(tableName, columns, [options], callback) ⇒ Operation

Create a table in database when given an array of columns and a table_name.

Param	Type	Description
tableName	string	The name of the table to create
columns	array	An array of form columns
[options]	object	Optional options argument
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

insertTable('table_name', state => state.data.map(
  column => ({
    name: column.name,
    type: column.type,
    required: true, // optional
    unique: false, // optional - to be set to true for unique constraint
  })
));

---

modifyTable

modifyTable(tableName, columns, [options], callback) ⇒ Operation

Alter an existing table in the database.

Param	Type	Description
tableName	string	The name of the table to alter
columns	array	An array of form columns
[options]	object	Optional options argument
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

modifyTable('table_name', state => state.data.map(
  newColumn => ({
    name: newColumn.name,
    type: newColumn.type,
    required: true, // optional
    unique: false, // optional - to be set to true for unique constraint
  })
));

---

sql

sql(sqlQuery, [options], callback) ⇒ Operation

Execute an SQL statement

Param	Type	Description
sqlQuery	function	a function which takes state and returns a string of SQL.
[options]	object	Optional options argument
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callback	function	(Optional) callback function

Example

sql(state => `select(*) from ${state.data.tableName};`, { writeSql: true })

---

upsert

upsert(table, uuid, record, [options], callback) ⇒ Operation

Insert or update a record using ON CONFLICT UPDATE

Param	Type	Description
table	string	The target table
uuid	string	The uuid column to determine a matching/existing record
record	object	Payload data for the record as a JS object or function
[options]	object	Optional options argument
[options.setNull]	string	A string value that specifies the behavior for inserting null values.
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]	boolean	A boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callback	function	(Optional) callback function

Example

upsert(
  'users', // the DB table
  'ON CONSTRAINT users_pkey', // a DB column with a unique constraint OR a CONSTRAINT NAME
  { name: 'Elodie', id: 7 },
  { setNull: ["''", "'undefined'"], writeSql:true, execute: true, logValues: true }
)

---

upsertIf

upsertIf(logical, table, uuid, record, [options], callback) ⇒ Operation

Insert or update a record based on a logical condition using ON CONFLICT UPDATE

Param	Type	Description
logical	string	a data to check existing value for.
table	string	The target table
uuid	string	The uuid column to determine a matching/existing record
record	object	Payload data for the record as a JS object or function
[options]	object	Optional options argument
[options.setNull]	string	A string value that specifies the behavior for inserting null values.
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]	boolean	A boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callback	function	(Optional) callback function

Example

upsertIf(
  dataValue('name'),
  'users', // the DB table
  'ON CONSTRAINT users_pkey', // a DB column with a unique constraint OR a CONSTRAINT NAME
  { name: 'Elodie', id: 7 },
  { writeSql:true, execute: true }
)

---

upsertMany

upsertMany(table, uuid, data, [options], callback) ⇒ Operation

Insert or update multiple records using ON CONFLICT UPDATE and excluded

Param	Type	Description
table	string	The target table
uuid	string	The uuid column to determine a matching/existing record
data	array	An array of objects or a function that returns an array
[options]	object	Optional options argument
[options.setNull]	string	A string value that specifies the behavior for inserting null values.
[options.writeSql]	boolean	A boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]	boolean	A boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]	boolean	A boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callback	function	(Optional) callback function

Example

upsertMany(
  'users', // the DB table
  'email', // a DB column with a unique constraint OR a CONSTRAINT NAME
  [
    { name: 'one', email: 'one@openfn.org' },
    { name: 'two', email: 'two@openfn.org' },
  ]
 { logValues: true }
)

---