Writing PL/PgSQL Scripts

PL/PgSQL Code in the Script Engine

In Eliona, scripts can be written not only in Python but also in PL/PgSQL. This allows for the direct processing of SQL queries within the script engine.

---

Script Definition

When you create a new PL/PgSQL script in the script engine, a template function is automatically suggested. This template serves as a starting point for script creation.

DO $$
DECLARE
    -- Variables can be declared here
BEGIN
    -- SQL queries and operations can be executed here
END $$;

Example: Update Timestamp

The following example shows how a script retrieves the current timestamp (NOW()) from the database and updates it for a specific asset (asset_id = 1) in the heap table:

DO $$
    DECLARE r timestamptz;
BEGIN
    SELECT NOW() INTO r;
    UPDATE heap SET ts = r WHERE asset_id = 1;
END$$;

▷ The script saves the current timestamp in the heap table for the asset with the ID 1

Note on security: Direct database changes are effective immediately and cannot be undone.