PostgreSQL triggers

SELECT trigger and action

Both the SELECT trigger and action operate in the same manner.

However, 'SELECT' is referred to as either a trigger or action depending on the scenario. SELECT is:

  • A trigger when used in the first step/component of a flow, and

  • An action when used in any other step/component of the flow.

PostgreSQL - first step

Following configuration options are available:

  • SQL Query - Specify your SELECT query, which in turn should return 0 or more data back to you. There is no limit on number of rows returned by your SELECT queries. Results are fetched in batches of 1000 and pushed to the next component. You can also use variables from incoming messages in the templates.

  • Bundle results in batches - This option influences how your results are returned to the next component. Your query results can be organised as:

    • A stream (useful for asynchronous processing) - each row in your results is placed in a single message.

    • A whole batch (when you do not expect too many rows to be returned in the output) - all row results are grouped as batch (for up to 1000 rows).

For example, if you have an SQL query that retrieves 400 rows, then if Bundle results in batches were enabled, a single message would be returned containing an array of 400 elements:

{
  "values" : [
    {"id": 1},
    {"id": 2},

    {"id": 400}
  ]
}

If no records were retrieved, a message with an empty array would be returned. These responses can be useful, especially when working with request-response tasks.

If the Bundle results in batches check box is cleared (which is the case by default) then a single message is generated by per row returned. Therefore, in example above, 400 messages would be returned. And if the SQL query retrieved no data, then no messages would be returned.

Known limitation

The SELECT trigger and action does not support transactions.