Bei der Entwicklung einer Anwendung zur Berücksichtigung von Zeitnormen wurde es notwendig, eine Pivot-Tabelle mit einer Auswahl von Daten aus der bestehenden zu erstellen.
Als Datenbank wurde PostgreSQL verwendet.
Die Quelltabelle base_table wird wie folgt vereinfacht:
Tabellenfelder haben die folgenden Typen:
- id - Ganzzahl, automatisch inc;
- **Datum datum;
- Menge - ganze Zahl;
- hours_count - Zeit
Den restlichen Feldern wird einfach der Typ Text zugewiesen.
Es werden Vereinfachungen eingeführt, um den Fokus nicht auf die Struktur der Tabelle zu legen.
Daraus müssen in der Pivot-Tabelle "new_table" folgende Daten selektiert werden: Auftrag, Ausstattung, Produkt, SUM(Menge), SUM(Stunden_Anzahl) , folgende Felder müssen auch im Pivot vorhanden sein Tabelle Daten (Ganzzahl) und Zustand (boolean) . In der neuen Tabelle müssen nicht nur Werte aus den Spalten Bestellung, Ausstattung, Produkt eingegeben werden, sondern auch gruppierte.
Zum leichteren Verständnis, wenn ich ein wenig vorausschaue, gebe ich den vorgeschlagenen Anforderungscode an, der die Aufgabe erfüllt:
INSERT INTO new _table ( order , equip ment , p roduct , o quantity , time_sum ) SELECT order_number , equipment , p roduct , SUM ( quantity ), SUM ( hours_count ) FROM base _table GROUP BY order , equipment , p roduct HAVING NOT EXISTS ( SELECT order , equip ment , p roduct FROM new _table WHERE order = order AND equip ment = equipment AND p roduct = p roduct );
Anfangs gab es Überlegungen, eine separate Tabelle „new_table“ als Übersichtstabelle und im manuellen Modus in einem Programm zu erstellen, das eine Datenbank verwendet, die eine SQL-Abfrage verwendet, um die erforderlichen Daten aus der Haupttabelle auszuwählen. Dennoch blieb die Frage, wie nach der Auswahl neue Datensätze in “base_table“ angelegt werden sollen, da die Daten laufend eingetragen werden und in “new_table“ ständig aktualisiert werden müssen.
Nach einer Diskussion im Forum der EVILEG-Website über mögliche Optionen zur Lösung der Aufgabe schlug der Ersteller einen Ausweg aus dieser Situation vor, indem er auf Änderungen der Daten reagierte in der Haupttabelle.
Auf dieser Grundlage wurde entschieden, dass es notwendig ist, die Daten in den Tabellen zu synchronisieren, damit beim Erstellen, Bearbeiten oder Löschen von Datensätzen in der “base_table“ die entsprechenden Änderungen auch in der Pivot-Tabelle auftreten “ neue_tabelle“ .
Tabellenansicht “new_table“ :
Es wurde deutlich, dass hierfür Triggerfunktionen mit der Verarbeitung von Ereignissen zum Anlegen, Bearbeiten und Löschen von Daten aus der Basistabelle verwendet werden müssen.
Gehen wir zur Betrachtung des Prozesses der Bildung solcher Funktion mit ihrer Beschreibung über.
Das Folgende ist die Deklaration der Triggerfunktion:
CREATE TRIGGER select_to_table BEFORE INSERT OR UPDATE OR DELETE ON base _table FOR ROW EXECUTE PROCEDURE process_select_to ();
Die erste Zeile enthält eine Funktionsdeklaration, nennen wir sie "select_to_table" .
Die zweite Zeile gibt an, bei welchen Aktionen auf der Basistabelle die Funktion aufgerufen werden soll. Das Schlüsselwort BEFORE bedeutet, dass die Funktion vor dem Erstellen eines neuen Datensatzes ( INSERT ), dem Bearbeiten ( UPDATE ) oder dem Löschen eines bestehenden Datensatzes ( DELETE ) in der Tabelle „base_table“ aufgerufen werden muss.
Anstelle von BEFORE kann auch AFTER angegeben werden, was bedeutet, dass die Funktion aufgerufen wird, nachdem die oben genannten Ereignisse eingetreten sind.
Es ist erwähnenswert, dass die offizielle PostgreSQL-Dokumentation die Verwendung von BEFORE als Schlüsselwort empfiehlt.
Die dritte Zeile gibt FOR ROW oder FOR EACH ROW an. Der erste Eintrag bedeutet einen Funktionsaufruf für eine Zeile und der zweite - einen für jede Zeile der Tabelle.
Und der letzte Parameter nach dem Schlüsselwort PROCEDURE ist der Name der aufgerufenen Funktion selbst. Eine Funktion mit diesem Namen muss vor der Deklaration deklariert werden oder bereits existieren, sonst wird sie einen Fehler auslösen.
Kommen wir nun zur allgemeinen Ansicht der Funktion selbst.
CREATE OR REPLACE FUNCTION «имя функции» RETURNS TRIGGER AS $select_to$ BEGIN -- тело функции -- RETURN NULL ; -- тип возвращаемого значения END ; $select_to$ LANGUAGE plpgsql;
Die Funktion muss die obige Form mit der obligatorischen Angabe des Rückgabewertes haben. Der Rückgabetyp ist NULL, da der Trigger keine Aktion für Tabellen ausführt. Alle Arbeiten an Tabellen finden im Hauptteil der Funktion statt.
Die Art der Funktion sollte keine besonderen Fragen aufwerfen. Wer jedoch an einer detaillierteren Beschreibung interessiert ist, kann auf die offizielle Dokumentation verweisen.
Lassen Sie uns nun mit dem Schreiben des Körpers selbst fortfahren. Da wir zuvor angegeben haben, dass wir die Ereignisse INSERT , UPDATE und DELETE verarbeiten möchten, müssen wir auf jedes von ihnen eine Antwort geben. Um die Art der Operation in PostgreSQL zu bestimmen, gibt es eine Variable TG_OP , die den Namen der ausgeführten Operation enthält.
Auf dieser Grundlage schreiben wir eine Bedingung zur Überprüfung der Art der Operation, die die folgende Form haben wird:
IF ( TG_OP = 'DELETE' ) THEN -- операторы -- ELSEIF ( TG_OP = 'INSERT' ) THEN -- операторы -- ELSIF ( TG_OP = 'UPDATE' ) THEN -- операторы -- END IF ;
Schreiben wir nun den Code zum Löschen des Eintrags:
IF ( TG_OP = 'DELETE' ) THEN -- Удаление записи из таблицы new _table , которая удовлетворяет условию -- DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ; IF NOT FOUND THEN RETURN NULL ; -- если строки в new _table не существует -- END IF ; RETURN OLD ;
Ich denke, diese Abfrage benötigt keine detaillierte Beschreibung, außer einer Sache - dies ist das Schlüsselwort OLD, das es ermöglicht, die alten Werte von Datensätzen aus der Tabelle „base_table“ herauszufinden, dh vor ihnen werden geändert. Außerdem ist zu beachten, dass der Rückgabewert der Funktion zum Löschen von Datensätzen immer RETURN OLD sein muss.
Fahren wir mit dem Erstellen eines neuen Eintrags fort:
ELSEIF ( TG_OP = 'INSERT' ) THEN -- Создание новой записи в таблице new _table - - INSERT INTO new _table ( order , equipme nt , p roduct , quantity , time_sum VALUES ( NEW . order_number , NEW . equipment , NEW . part , NEW . otk , NEW . hours_count ); RETURN NEW ;
Auch die Anfrage selbst benötigt keine detaillierte Beschreibung. Ich werde nur sagen, dass es mit dem Wort NEW möglich ist, neue Werte von Datensätzen aus der Tabelle „base_table“ zu erhalten, und dass als Rückgabewert beim Erstellen oder Bearbeiten von Datensätzen NEW als Rückgabewert angegeben werden muss .
Jetzt ist es an der Reihe, den Datensatz zu aktualisieren:
ELSIF ( TG_OP = 'UPDATE' ) THEN -- Обновление записи в new _table -- UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct , quantity = NEW . quantity , time_sum = NEW . hours_count WHERE order = OLD . order AND equip ment = OLD . equipment AND p roduct = OLD . p roduct AND quantity = OLD . quantity AND time_sum = OLD . hours_count ; RETURN NEW ;
Für diese Anfrage sollte alles aus den vorherigen Beschreibungen klar sein. Jetzt ist es an der Zeit, den Code in seiner Gesamtheit zu betrachten.
CREATE OR REPLACE FUNCTION «имя функции» RETURNS TRIGGER AS $select_to$ BEGIN IF ( TG_OP = 'DELETE' ) THEN -- Удаление записи из new _table , которая удовлетворяет условию DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ; IF NOT FOUND THEN RETURN NULL ; -- если строки в new _table не существует END IF ; RETURN OLD ; ELSEIF ( TG_OP = 'INSERT' ) THEN -- C оздание новой записи в new _table INSERT INTO new _table ( order , equipme nt , p roduct , quantity , time_sum ) VALUES ( NEW . order_number , NEW . equipment , NEW . part , NEW . otk , NEW . hours_count ); RETURN NEW ; ELSIF ( TG_OP = 'UPDATE' ) THEN -- Обновление записи в new _table UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct , quantity = NEW . quantity , time_sum = NEW . hours_count WHERE order = OLD . order AND equip ment = OLD . equipment AND p roduct = OLD . p roduct AND quantity = OLD . quantity AND time_sum = OLD . hours_count ; RETURN NEW ; RETURN NULL ; END ; $select_to$ LANGUAGE plpgsql; CREATE TRIGGER select_to_table BEFORE INSERT OR UPDATE OR DELETE ON base _table FOR ROW EXECUTE PROCEDURE process_select_to ();
Nach Ausführung dieses Codes wird eine neue Triggerfunktion in der PostgreSQL-Datenbank registriert, um die Erstellung, Änderung oder Löschung von Datensätzen in „base_table“ zu handhaben. Ähnliche Operationen werden synchron mit den entsprechenden Datensätzen in “new_table“ durchgeführt.
Приветствую!