Gespeicherte Abfragen

Was ist das

Eine gespeicherte Abfrage ist ein benanntes, einzelnes SELECT mit deklarierten Parametern. Ansichten binden ihre Widgets an eine Abfrage, nie an rohes SQL. Aktionen lesen Daten über den Kommando-Schritt query.run. Und jede Kennzahl im Cockpit ist selbst eine gespeicherte Abfrage über dieselben abgeleiteten Tabellen, die auch Ansichten und Aktionen lesen. Für jede Zahl im System gibt es damit nur eine SQL-Quelle, statt derselben Abfrage mehrfach und mit kleinen Abweichungen über Widgets und Kommandos verstreut.

Die Abfrage läuft immer in einer Nur-Lese-Transaktion. Schreiben kann sie nicht, ganz gleich, was im SQL-Text steht: Ein INSERT oder UPDATE scheitert an der Transaktion selbst, nicht erst an einer Prüfung des Textes.

Wann einsetzen

Sie greifen zu einer gespeicherten Abfrage, sobald eine Ansicht oder eine Aktion Daten benötigt. Abteilungsübersichten, das Cockpit und Los-Warteschlangen an einer Maschine beziehen ihre Zahlen auf demselben Weg: über gespeicherte Abfragen auf abgeleitete Tabellen wie machines_current, lots_current oder step_times.

Erstellen Sie zuerst die Abfrage und binden Sie sie anschließend in ein neues Widget oder eine erweiterte Aktion ein. So bleibt das SQL außerhalb des Widgets oder Kommandos. Für eine einmalige Auswertung kann ein Admin das SQL in der Editorvorschau testen. Speichern Sie die Abfrage, sobald sie dauerhaft oder an mehreren Stellen benötigt wird.

Anatomie der Definition

Eine Abfrage-Definition hat fünf Felder:

name: der Bezeichner, unter dem Ansichten, Aktionen und der Assistent die Abfrage referenzieren. Er folgt dem Muster ^[a-z][a-z0-9_]{0,63}$ und darf nicht der Name einer eingebauten Abfrage sein.
description: eine kurze, menschenlesbare Beschreibung, die im Katalog und im Schema-Browser erscheint.
sql: genau ein SELECT. Mehrere Anweisungen oder ein schreibender Befehl sind kein gültiges SQL für eine gespeicherte Abfrage.
params: die Liste der Parameter, je einer als {name, type} mit type aus text, int, number oder bool. Die Reihenfolge der Liste muss der Nummerierung der Platzhalter $1, $2, … im SQL entsprechen.
min_role: die Mindestrolle, die eine Abfrage ausführen darf. Anlegen und Ändern einer Definition kann dagegen ausschließlich ein Admin, unabhängig von min_role.

Nur Admins verwalten diese Definitionen. Der Menüpunkt „Gespeicherte Abfragen“ unter Verwaltung enthält eine SQL-Vorschau, die das SQL vor dem Speichern testweise ausführt. Hinzu kommen ein Schema-Browser mit allen Tabellen und Spalten sowie ein FK-Diagramm der Tabellenbeziehungen. Wenn Sie im Schema-Browser einen Namen auswählen, übernimmt der Editor ihn in das SQL.

Bediener und Planer sehen diese Werkzeuge nicht. Sie nutzen fertige Abfragen nur über Widgets und im Rahmen der Rolle, die min_role erlaubt.

Beispiele

Die folgenden drei Abfragen sind unverändert dem Demoprofil der Kaltumformung entnommen, genau die Definitionen, mit denen die Demo tatsächlich arbeitet.

-- machine_detail, params: machine_id
SELECT id, name, state, state_reason, capabilities, tooling FROM machines_current WHERE id = $1

Mit dem Assistenten

Ich brauche eine Detail-Abfrage für eine Maschine über ihre id.

Ein einziger Parameter, machine_id vom Typ text, füllt den Platzhalter $1. Die Abfrage liest id, name, state, state_reason, capabilities und tooling aus machines_current für genau diese eine Maschine. Ein Detail-Widget bindet sich an diese Abfrage und übergibt die Maschine als Kontext-Wert mit.

-- kpi_oee, params: from, to
WITH win AS (
  SELECT COALESCE((NULLIF($1,'')::date)::timestamp AT TIME ZONE plant_tz(), now() - interval '30 days') AS t0,
         COALESCE((NULLIF($2,'')::date + 1)::timestamp AT TIME ZONE plant_tz(), now()) AS t1
), w AS (
  SELECT sw.shift, GREATEST(sw.started_at, win.t0) AS s0, LEAST(sw.ended_at, win.t1) AS s1
  FROM win, shift_windows((win.t0 AT TIME ZONE plant_tz())::date - 1,
                          (win.t1 AT TIME ZONE plant_tz())::date) sw
  WHERE sw.ended_at > win.t0 AND sw.started_at < win.t1
), iv AS (
  SELECT l.state,
         EXTRACT(EPOCH FROM (LEAST(COALESCE(l.ended_at, now()), w.s1) - GREATEST(l.started_at, w.s0))) AS secs
  FROM machine_state_log l
  JOIN w ON l.started_at < w.s1 AND COALESCE(l.ended_at, now()) > w.s0
  WHERE l.state <> 'maintenance'
), avail AS (
  SELECT SUM(secs) FILTER (WHERE state = 'running') AS run_secs, SUM(secs) AS sched_secs FROM iv
), prod AS (
  SELECT SUM((st.qty_good + st.qty_scrap) * COALESCE((rs.s ->> 'run_per_unit_s')::numeric, 0)) AS ideal_secs,
         SUM(st.qty_good)::numeric AS good,
         SUM(st.qty_good + st.qty_scrap)::numeric AS total
  FROM step_times st
  JOIN w ON st.completed_at > w.s0 AND st.completed_at <= w.s1
  JOIN orders_current o ON o.number = st.order_number
  LEFT JOIN LATERAL (
    SELECT s FROM jsonb_array_elements(o.routing) s WHERE (s ->> 'seq')::int = st.step
  ) rs ON TRUE
) SELECT ROUND(100
  * COALESCE(a.run_secs / NULLIF(a.sched_secs, 0), 0)
  * LEAST(1, COALESCE(p.ideal_secs / NULLIF(a.run_secs, 0), 0))
  * COALESCE(p.good / NULLIF(p.total, 0), 0), 0) AS value
FROM avail a, prod p

Mit dem Assistenten

Baue mir eine OEE-Kennzahl mit wählbarem Zeitraum.

Zwei Parameter, from und to, beide vom Typ text, spannen den Zeitraum auf. Leer bleiben darf jeder der beiden: Das SQL selbst prüft mit COALESCE und NULLIF auf eine leere Zeichenkette und setzt dafür die letzten 30 Tage an. Optionale Parameter im Sinne des Schemas gibt es hier nicht, nur einen Parameter, dessen leerer Wert im SQL sinnvoll behandelt wird.

-- machines_by_state
SELECT state, count(*) AS count FROM machines_current WHERE lifecycle = 'active' GROUP BY state ORDER BY count DESC

Mit dem Assistenten

Zähle mir die Maschinen je Zustand, ohne Parameter.

Keine Parameter. Die Abfrage gruppiert die aktiven Maschinen aus machines_current nach state und zählt sie je Gruppe, absteigend sortiert nach Anzahl. Eine typische Quelle für ein Balken-Widget in einer Abteilungs-Übersicht.

Mit dem Assistenten arbeiten

Jedes Beispiel oben zeigt auch den Prompt, mit dem Sie dieselbe Abfrage über den Assistenten anlegen lassen können. Sie müssen SQL, Parameter und min_role dann nicht manuell entwerfen. Vor der Vorlage führt der Assistent das Kandidaten-SQL in einer begrenzten Vorschau mit Ersatzwerten für jeden Parameter aus und prüft dessen Ausführbarkeit. Den Ablauf von der Prüfung bis zur Schaltfläche „Übernehmen“ beschreibt das Kapitel Der KI-Assistent.

Fallstricke

Parameter-Reihenfolge. Die params-Liste muss der Nummerierung der $-Platzhalter im SQL entsprechen. Ein vertauschtes Paar bleibt beim Speichern unbemerkt und zeigt sich erst zur Laufzeit, wenn die Werte an der falschen Stelle landen.
Keine optionalen Parameter. Jeder deklarierte Parameter muss beim Aufruf einen Wert bekommen. Wer einen Zeitraum optional halten will, behandelt eine leere Zeichenkette im SQL selbst, genau wie kpi_oee es vormacht.
Gesperrte Namen. Der Name einer eingebauten Abfrage lässt sich nicht für eine eigene Definition wiederverwenden.
min_role ist die einzige Ausführungsschranke. min_role: operator bedeutet, dass jede Station die Abfrage über ein Widget ausführen kann. Für Auswertungen mit sensiblen Zahlen wie Kosten oder Personal gehört planner oder admin in dieses Feld.

Wie eine Aktion eine Abfrage über query.run einbindet, zeigt das Kapitel Aktionen. Wie das Cockpit gespeicherte Abfragen zu Kennzahlen macht, beschreibt das Kapitel Kennzahlen & Cockpit. Und woher die abgeleiteten Tabellen selbst stammen, erklärt das Kapitel Datenmodell.