postgresql@8.0.0

describeTable(tableName, [options])

findValue(filter)

insert(table, record, [options])

insertMany(table, records, [options])

insertTable(tableName, columns, [options])

modifyTable(tableName, columns, [options])

sql(sqlQuery, [options])

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

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

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

This adaptor exports the following from common:

alterState

arrayToString()

as()

assert()

combine()

cursor()

dataPath()

dataValue()

dateFns

each()

field()

fields()

fn()

fnIf()

group()

lastReferenceValue()

merge()

sourceValue()

Functions

describeTable

describeTable(tableName, [options]) ⇒ Operation

List the columns of a table in a database.

Param	Type	Description
tableName	string	The name of the table to describe
[options]	ExecutionOptions	Execution options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Describe a table

describeTable('clinic_visits')

---

findValue

findValue(filter) ⇒ Operation

Fetch a uuid key given a condition

Param	Type	Description
filter	FindValueFilter	A filter object with the lookup table, a uuid and the condition

This operation writes the following keys to state:

State Key	Description
data	the value of the found uuid
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Find a user by first name

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

---

insert

insert(table, record, [options]) ⇒ 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]	GeneralOptions	Shared options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Insert a record

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

---

insertMany

insertMany(table, records, [options]) ⇒ 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]	GeneralOptions	Shared options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Insert many records

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

---

insertTable

insertTable(tableName, columns, [options]) ⇒ 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]	ExecutionOptions	Execution options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

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]) ⇒ 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]	ExecutionOptions	Execution options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

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]) ⇒ Operation

Execute an SQL statement

Param	Type	Description
sqlQuery	string | SqlQueryConfig	SQL query string or a query config object.
[options]	ExecutionOptions	Execution options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Text-only Query

sql('SELECT * FROM users;');

Example: Text-only Query with writeSql option

sql("select id from users where first_name = 'Mamadou'", { writeSql: true });

Example: Parameterized Query

sql("INSERT INTO users(name, age) VALUES ($1, $2);", { values: ["Alice", 25]});

Example: Format query with util.format

sql(util.format('INSERT INTO users(name, age) VALUES (%L, %L);', 'Alice', 25));

Example: Prepared Statements

sql({
  // give the query a unique name
  name: "fetch-user",
  text: "SELECT * FROM user WHERE id = $1",
  values: [1],
});

---

upsert

upsert(table, uuid, record, [options]) ⇒ 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]	GeneralOptions	Shared options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Insert or update a record

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,
    logValues: true,
  }
);

---

upsertIf

upsertIf(logical, table, uuid, record, [options]) ⇒ 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]	GeneralOptions	Shared options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Insert or update a record conditionally

upsertIf(
  $.data.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 }
);

---

upsertMany

upsertMany(table, uuid, data, [options]) ⇒ 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]	GeneralOptions	Shared options. (OpenFn only)

This operation writes the following keys to state:

State Key	Description
data	the parsed result rows
result	the result from a successful query
references	an array of all previous data objects used in the job

Example: Insert or update multiple records

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' },
  ]
)

---