Help Center

Menu

PostgreSQL actions

SELECT action

Refer to the SELECT Action and Trigger section of the PostgreSQL triggers page.

INSERT/UPDATE/DELETE action

This action inserts, updates or deletes some data in the datasbase. The returned value is ignored and the number of affected rows can be seen in the log file.

PostgreSQL - INSERT/UPDATE/DELETE Action

The following configuration option is available:

  • SQL Query - specify your INSERT/UPDATE/DELETE query in this text box. Returned data is ignored, so this component just pushes the original message to the next component. You can use variables from incoming messages in the templates, see section below on how it works.

Known limitations

This action does not support transactions.

INSERT bulk action

This action executes a bulk insert query in one transaction. An incoming message needs to contain a body with an array of objects.

Configuration field

INSERT Bulk action

Table name

Specify the name of the table (to which multiple values will be inserted) in the Table Name field.

  • Columns - specify the name of the columns in which corresponding values will be inserted.

  • Input metadata - Values - specify an array of objects, where each object contains values that will be inserted into corresponding columns.

For example, assume you need to execute the following query:

INSERT INTO itemstable(id, text) VALUES (1, 'First item'), (2, 'Second item')

Therefore, you need specify the following information in the fields on this page:

  • Table Name - itemstable

  • Columns - id, text

  • Values:

    [
  {
    id: 1,
    text: 'First item'
  },
  {
    id: 2,
    text: 'Second item'
  }
]

If there is a problem with the query’s execution, any changes are rolled back.

SQL query action

Expert mode. This action executes an SQL query or SQL script.

Configuration field

SQL query

Specify your SQL expression in the SQL Query field for further execution. You can specify a single SQL query or several in this field. If you specify several, separate each SQL query with a semicolon (;). All SQL queries specified in this field are executed in one transaction. If there is a problem with any of the queries' executions, all changes are rolled back.

To use prepared statements in your query, define prepared statement variables using the format sqlVariableName = @MetadataVariableName:type, where:

  1. sqlVariableName - is the variable name in an SQL expression;

  2. MetadataVariableName - is the variable name in metadata (this can be the same as the sqlVariableName value);

  3. type - is the type of variable, of which the following types are supported:

    • string (the default type if this value is omitted)

    • number

    • boolean

For example, with the SQL expression:

SELECT * FROM tableName WHERE column1 = 'text' AND column2 = 15

you need to use following template:

SELECT * FROM tableName WHERE column1 = @column1:string AND column2 = @column2:number

and put values into generated metadata.

SQL Query

Input metadata

Input metadata is generated from the SQL Query configuration field, assuming this field contains at least one defined value.

Output metadata

The output metadata is an array of arrays, containing the result of query execution, which depends on the count of SQL queries that were executed. If the execution does not retrieve any results, the output metadata returns an empty array.

For example, with the SQL script:

INSERT INTO tableOne (column1, column2) VALUES ('value1', 'value2');
SELECT * FROM table2;

the first SQL query:

INSERT INTO tableOne (column1, column2) VALUES ('value1', 'value2')

does not return any values, whereas the second SQL query:

SELECT * FROM table2

returns two records.

Therefore, the output metadata for this example is:

[
  [],
  [
    {
      "col2": 123,
      "col1": "abc"
    },
    {
      "col2": 456,
      "col1": "def"
    }
  ]
]

SQL injection action

Expert mode. This action executes an SQL injection expression.

You can not use a prepared statement with this action. To do so, use an SQL query action.

Input metadata

Input metadata contains two fields:

  • SQL Expression;

  • Number of retries in case of deadlock transaction.

SQL expression

An SQL query, SQL script or a set of SQL queries from the previous step can be placed in an SQL expression. If you specify several, separate each SQL query with a semicolon (;). All SQL queries specified are executed in one transaction. If there is a problem with any of the queries' executions, all changes are rolled back.

For example, you have a file with a defined SQL script, which you want to execute with this component. First, in the previous step, use another component to read that file, which returns a value like this:

{
  "query_string": "INSERT INTO tableOne (column1, column2) VALUES ('value1', 'value2'); SELECT * FROM table2"
}

and then in this component’s action you need put query_string (or some JSONata expression) to Sql Injection string:

Integrator mode

Number of retries in case of deadlock transaction

Specify the maximum number of times the action is retried, to help resolve any lock issues in the event of a deadlocked transaction. The delay between each retry is 1 second. The default value for this configuration field is 0, which deactivates this feature.

Output metadata

The output metadata is an array of arrays, containing the result of query execution, which depends on the count of SQL queries which were executed. If the execution does not retrieve any results, the output metadata returns an empty array.

For example, with the SQL script:

INSERT INTO tableOne (column1, column2) VALUES ('value1', 'value2');
SELECT * FROM table2;

the first SQL query:

INSERT INTO tableOne (column1, column2) VALUES ('value1', 'value2')

does not return any values and the second SQL query:

SELECT * FROM table2

returns two records.

Therefore, the output metadata for this example is:

[
  [],
  [
    {
      "col2": 123,
      "col1": "abc"
    },
    {
      "col2": 456,
      "col1": "def"
    }
  ]
]