Connect to PostgreSQL

El Mirador can run SQL against a PostgreSQL database whenever an operator uses a widget - to log a setpoint change, record who pressed what, or store a recipe value next to the PLC.

Connect

1. Open Project Settings → Connections → Databases.
2. Enter Host / IP, Port (5432 by default), Database name, Username and Password.
3. Click Connect. The status changes to Online.

Create a database user just for El Mirador with only the rights it needs - for example INSERT on one log table - instead of connecting as the database owner.

Run SQL when a widget is used

Button, Switch, Slider and Field have a Database tab in Properties. The SQL you enter there runs when the widget is clicked or changed:

UPDATE settings SET speed = $1 WHERE id = 1

Each parameter - $1, $2 and so on - is either the widget's own new value or a one-shot read of an ADS variable at that moment. That lets you store PLC values together with what the operator did:

INSERT INTO setpoint_log (changed_at, setpoint, tank_level)
VALUES (now(), $1, $2)

Here $1 is the slider's new value and $2 a read of MAIN.fTankLevel. For a button, the widget's own value is true.

Example: a setpoint log

Create the table once, for example in psql:

CREATE TABLE setpoint_log (
    id          bigserial PRIMARY KEY,
    changed_at  timestamptz NOT NULL,
    setpoint    integer     NOT NULL,
    tank_level  real
);

Then put the INSERT from above on the setpoint slider of the example dashboard. Every time an operator moves it, a row is added.

Always pass values as $1, $2, … parameters - never build SQL text out of them. Parameters are sent separately from the SQL, so an operator's input can't change what the statement does.

Troubleshooting

• Connection refused or timeout - check host and port, that PostgreSQL accepts network connections (listen_addresses in postgresql.conf), and that a pg_hba.conf entry allows this PC.
• Password authentication failed - check username and password; they're case-sensitive.
• Permission denied for table - grant the El Mirador user the rights its SQL needs, e.g. GRANT INSERT ON setpoint_log TO mirador;.