Postgres Executer Documentation

Version: 2.1.0

Retrieved: 2025-10-09 15:16:10

PostgresExecuterModule

Overview

The PostgresExecuterModule allows you to execute SQL commands and queries against a PostgreSQL database. When the Allow template syntax in SQL statement setting is enabled, the module supports executing SQL statements with template syntax based on the incoming message. Use this feature with caution, as using non-sanitized values can pose security risks!

Settings

Credential

This module needs a credential of type 'Connection string' to connect to the database server. A minimal connection string is shown below. Depending on the setup of your database server, additional parameters may be needed. Please consult the documentation for your server.

Server=192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;

Input

Any input message can be used to trigger the execution of the SQL statement. Properties in the message can be referenced in the SQL statement using @ syntax. If message properties are used in the SQL statement, they must be present on the incoming message.

Output

An output message will be sent each time the module receives a message.

Examples

Example 1

Select and return all rows containing a temp larger than data.temp.

Settings

• Target Property: out
• SQL Statement: SELECT * FROM temps WHERE temp > @data.temp;
• Request Type: query

Input

{
  "data": {
      "temp": 50
  }
}

Output

{
  "data": {
      "temp": 50
  },
  "out": [
    {
        "name": "sensor3",
        "temp": 129
    },
    {
        "name": "sensor4",
        "temp": 489
    }
  ],
  "crosser": {
      "success": true
  }
}

Example 2

Delete all readings in temps.

Settings

• Target Property: out
• SQL Statement: DELETE FROM temps;
• Request Type: command

Input

{
  "data": {
      "temp": 50
  }
}

Output

{
  "data": {
      "temp": 50
  },
  "out": {
      "rowCount": 0
  },
  "crosser": {
      "success": true
  }
}

Example 3

Select columns from table where condition is met with template syntax.

Settings

• Target Property: result
• SQL Statement: SELECT {columns} FROM {table} WHERE {condition}
• Request Type: query
• Allow template syntax in SQL statement: True

Input

{
  "columns": "Speed, Temperature",
  "table": "SensorData",
  "condition": "timestamp > @timestamp",
  "timestamp": "2023-01-01T00:00:00Z"
}

Output

{
  "columns": "Speed, Temperature",
  "table": "SensorData",
  "condition": "timestamp > @timestamp",
  "timestamp": "2023-01-01T00:00:00Z",
  "result": [
    {
        "Speed": 60,
        "Temperature": 22
    },
    {
        "Speed": 70,
        "Temperature": 25
    }
  ],
  "crosser": {
      "success": true
  }
}

Example 4

Take the full query from the incoming message.

Settings

• Target Property: result
• SQL Statement: {query}
• Request Type: query
• Allow template syntax in SQL statement: True

Input

{
  "query": "SELECT * FROM SensorData LIMIT 5"
}

Output

{
  "crosser": {
    "success": true
  },
  "query": "SELECT * FROM SensorData LIMIT 5",
  "result": [
    {
      "Temperature": 90,
      "Speed": 12
    },
    {
      "Temperature": 23,
      "Speed": 54
    },
    {
      "Temperature": 23,
      "Speed": 66
    },
    {
      "Temperature": 34,
      "Speed": 23
    },
    {
      "Temperature": 64,
      "Speed": 54
    }
  ]
}