The PostgreSQL Plug-in
With the PostgreSQL database for networked production
PostgreSQL is a highly flexible and powerful open source database that offers a wide range of features. With full transaction support, extensible architecture and a variety of data types, PostgreSQL enables developers to efficiently manage complex data structures. Thanks to its scalability options, security features and high availability solutions, PostgreSQL (What is PostgreSQL?) is suitable for both small applications and large enterprise systems. Support for complex queries and the ability to define integrity rules and triggers make PostgreSQL a popular choice for applications that require a reliable and powerful database.
It can be that simple.
PostgreSQL connection via drag & drop
Connecting your PostgreSQL database is easy and intuitive with the OPC Router using drag & drop. The screenshot shows the exemplary connection of a PostgreSQL database. A classic use case is the coupling of SQL to OPC, shown here with a stored procedure call.
Automated data exchange with PostgreSQL
- Achieve more with coupled systems:
By linking the PostgreSQL database via the OPC Router, you enable the distribution and collection of lots of valuable data between systems. This eliminates stand-alone systems and promotes automated data exchange in networked production. - Industry 4.0 with PostgreSQL:
Networking PostgreSQL to your systems opens up many paths for data and thus enables the implementation of Industry 4.0. A classic application here is the coupling of PostgreSQL to OPC. - About the OPC Router:
With the OPC Router, you can graphically implement the networking of your systems. Numerous plug-ins make it middleware in the industrial environment. It is characterized by simple configuration, sophisticated monitoring and robustness.
PostgreSQL Plug-in application examples
Production data acquisition
With the OPC Router, you create the basis for your production data acquisition system. The various plug-ins allow you to load data from a wide variety of systems and transfer it to the target system for evaluation. Typical targets are MES systems (Manufacturing Execution System) or ERP systems (Enterprise Resource System (e.g. SAP)). Thanks to the simple configuration, a PostgreSQL can be used as the central operating database and the systems with the data sources are connected graphically and without programming. Existing PDAs can often be connected via a database connection and supplied with data. A typical example of data that is collected for production data acquisition is control data from PLC systems. Controllers are connected via the standardized OPC UA protocol (with the OPC UA Client plug-in).
Lightweight data transmission
The MQTT communication protocol can be used with the OPC Router for lightweight and fast data transfer to the PostgreSQL database. Data from the field level is sent to the OPC Router via an MQTT broker. This data can then be sent to a PostgreSQL database for storage or further use. This connection is bidirectional with the OPC Router. Data can also be transferred from the database to the OPC Router and then via MQTT to the field level, for example to an edge device, or to the cloud, such as Azure.
Connecting Systems with the OPC Router SQL Server Plug-in
Transfer Objects
The transfer object of the PostgreSQL plug-in enables access to tables, views and stored procedures.
Tables
Select, update, insert and delete actions are available for the tables. Once data records have been processed, they can be marked as “Transferred” by the OPC Router.
Views
Views can only be accessed as select queries.
Stored procedure
When accessing a stored procedure, the OPC Router executes an Execute. Input parameters can be passed.
Trigger
The PostgreSQL plug-in has a database trigger.
Database trigger
The database trigger checks a column of a table for changes. In the event of changes, the OPC Router is notified by change notification and then triggers the connection.
The database trigger does not have any outgoing data fields.
Transfer state trigger
The transfer state trigger searches for data records that have not yet been processed. The values of the data records can be output directly. Finally, the transactions are marked as successful or unsuccessful. If a transfer fails, the trigger can repeat the retrieval of the value.
Browsing
The PostgreSQL plug-in can browse the available objects from the database.
Tables and views
All tables and views accessible to the user are read from PostgreSQL. The columns and their types are then determined and made available for selection.
Stored procedures
The available stored procedures are also read. The input and output parameters are read out for each procedure and made available for selection, including the type specification.