Vorschlag: Tutorial zum Senden von E-Mails

Florian

Erneut besten Dank für eure Erklärungen und das Code-Beispiel. Leider schaffe ich es bisher dennoch nicht, eine E-Mail zu erzeugen. Das habe ich bisher gemacht:

Die Backend Funktion send_mail habe ich als Trigger markiert und maximal vereinfacht:

$BODY$
DECLARE
	subject text := 'Neue Bestellung';
	body text := 'Dies ist ein Test Body';
	to_list text := 'my@email.com';
BEGIN
	PERFORM instance.mail_send(subject,body,to_list);
	RETURN 0;
END;
$BODY$

Dann habe ich die Funktion als Trigger bei der Relation order eingetragen mit Startet AFTER, INSERT 1, UPDATE 0, DELETE 0, EACH ROW 0, DEFERRED 0. Wenn ich nun aber versuche, eine neue order zu erstellen, kommt die folgende Fehlermeldung:
ERROR: cannot return non-composite value from function returning composite type (SQLSTATE 42804)

Ich hatte die Funktion überhaupt nur deshalb aufs Minimum vereinfacht, weil ich mit der von Fabian vorgeschlagenen Funktion zuvor schon eine andere Fehlermeldung erhalten hatte:
ERROR: function instance.mail_send(text, text, text, text, text, integer, uuid) does not exist (SQLSTATE 42883)

Könnt ihr mit den Fehlermeldungen etwas anfangen und mir vielleicht sogar sagen, was ich anders machen müsste?

gabriel

Florian
Mit Postgres PL/SQL muss man erstmal warm werden (das ist die Sprache, mit der Backend-Funktionen ausgeführt wird).

Zu deinen Fehlern:
In dem Beispiel von @lsw-fabian hast du eine reguläre Funktion, die du bspw. aus einer Trigger-Funktion hättest starten können - deswegen hat sie den Return-Wert '0'. In einer Trigger-Funktion musst du immer entweder NEW (bei neuen oder veränderten Datensätzen) oder OLD (bei gelöschten Datensätzen) zurückgeben. Ansonsten kriegst du den Fehler: ERROR: cannot return non-composite value... - für einen INSERT-Trigger wäre RETURN NEW; korrekt.

Trigger-Funktionen sind für viele Anwendungsfälle extrem praktisch - leider haben sie aber spezielle Regeln. Das ist allgemein das Thema - PL/SQL ermöglicht sehr vieles, etwas Einarbeitung ist aber leider notwendig.

Wegen der 2. Fehlermeldung: @lsw-fabian hat den Parameter für CC-Adressen in instance.mail_send() vergessen - damit stimmen beim Aufruf die Parameter nicht. Alle Parameter nach Body sind optional (kannst du im Kontextmenü der Funktion nachschauen) - wenn sie angegeben werden, müssen Reihenfolge und Typ aber stimmen.

So wie du es hast, ist es richtig. Sobald der Rückgabewert in der Funktion auf RETURN NEW; gestellt ist, sollte es funktionieren.

lsw-fabian

Stimmt, da ist mir auf die schnelle ein Fehler unterlaufen. Danke fürs korrigieren @gabriel

Florian

Ich bin schon ein ganzes Stück vorangekommen (denke ich). Und zwar kann ich bereits vorhandene Datensätze auslesen und zum Mailversand verwenden. Was noch nicht gelingt, ist das Auslesen von Daten aus dem neuen NEW Datensatz. Hier mein Code:

$BODY$
DECLARE
	mail_event text:= 'order_new'; -- Bezeichnung in Relation `mail`
	mail_subject text;
	mail_body text;
	mail_recipient_id integer;
	mail_recipient text;

	order_orderer_id integer;
	order_orderer text;
	order_date_ready text;
	order_note text;

BEGIN

	SELECT subject, body, recipient 
	INTO mail_subject, mail_body, mail_recipient_id
	FROM {kus_physlab}.[mail]
	WHERE event = mail_event;

	SELECT email INTO mail_recipient
	FROM {kus_physlab}.[user]
	WHERE id = mail_recipient_id;

	
	-- Versuche, das Feld `note` aus der neuen `order` zu lesen:

	-- SELECT (kus_physlab.order.note) INTO order_note
	-- FROM {kus_physlab}.[order]
	-- WHERE (kus_physlab.order.id) = NEW.(kus_physlab.order.id);
	-- mail_body := 'order_note: ' || order_note;

	-- mail_body := 'order_note: ' || (NEW.note);
	
	-- mail_body := 'order_note: ' || NEW.note;


	-- Auch das Setzen von Text im neuen Datensatz gelingt nicht:

	NEW.test_text := 'Mein neuer Test-Text.';
	
	PERFORM instance.mail_send(mail_subject,mail_body,mail_recipient);

	RETURN NEW;
END;
$BODY$

Zwei Dinge funktionieren im Code einfach nicht:

Auf keine der drei auskommentierten Arten kann ich die Relation note aus dem neuen Datensatz order auslesen und im Body der Mail verschicken. Es erscheint immer die Fehlermeldung Column 'body" of relation "mail_spool' has no value or could not be resolved.
Ebenfalls schaffe ich es nicht, testweise einen Eintrag im NEW Datensatz zu verändern, siehe Quellcode. In der Dokumentation Trigger Functions wird z.B. NEW.last_date verwendet, weshalb ich dachte, dass ich mit meinem Befehl einfach den Inhalt des Attributs test_text verändern könnte.

Könnt ihr meinem obigen Code entnehmen, weshalb das nicht funktioniert?

Und noch eine dritte Frage: Was wäre eine gute Methode, Backend Funktionen zu debuggen, also zum Beispiel schnell sehen zu können, ob das Auslesen von Daten funktioniert. Ich schreibe das gerade immer in die zu versendende Mail, muss dann aber immer erst mal auf sie warten...

gabriel

Hallo Florian,

die Fehlermeldung Column 'body" of relation "mail_spool' has no value or could not be resolved kommt, wenn du beim Aufruf der Funktion instance.mail_send() einen NULL-Wert im Body-Parameter hast.

Aber ich glaube, ich sehe das Problem grade in einen deiner vorherigen Posts: Du musst deinen Trigger EACH ROW ausführen - anderweitig wird die Variable NEW nicht befüllt. Ein Trigger der nicht EACH ROW ausgeführt wird, läuft nur einmal auch wenn hunderte Datensätze erzeugt werden.

Stattdessen möchtest du mit jeden erzeugten Datensatz (mit jeder Row) etwas tun. Sobald du den Trigger auf EACH ROW umschaltest, kannst du sowohl NEW auslesen, als auch neue Werte zuweisen.

Bzgl. des Debuggings: Da gibt es mehrere Optionen. Für allgemeine Tests (nicht Trigger-bezogen) liefere ich Ergebnisse oder Fehler normalerweise als RETURN-Wert - dann kann ich direkt im Builder die Funktion testweise ausführen. Wenn du aber Code direkt in einem Trigger trouble-shooten möchtest, würde sich die instance.log_error()-Funktion anbieten.

Du hast bei den Platzhaltern (rechts) die Liste aller verfügbaren Instanzfunktionen und jeweils die Kontexthilfe dazu. Mit Log-Error lässt sich beliebiger Text ins REI3-Log schreiben, was du dann im Admin-Panel auslesen kannst.

Florian

Wow, es macht richtig Spass, ein bisschen an der Oberfläche von Postgres PL/SQL zu kratzen.

Es ist zwar kein richtiges MWE mehr, aber für alle, die Mails mit Datensätzen verschicken wollen, halte ich hier mal meinen funktionierenden Zwischenstand fest:

$BODY$
DECLARE
	mail_event text:= 'order_new';
	mail_subject text;
	mail_body text;
	mail_recipient text;

	order_orderer text;
	order_date_ready text;
	order_note text;
	order_experiments text;
BEGIN

	SELECT m.subject, m.body, u.email 
		INTO mail_subject, mail_body, mail_recipient
	FROM kus_physlab.mail m
		JOIN kus_physlab.user u ON u.id = m.user_id_recipient
	WHERE m.event = mail_event;

	SELECT u.nickname 
		INTO order_orderer
	FROM kus_physlab.user u
	WHERE u.id = NEW.user_id_orderer;
	
	order_date_ready := TO_CHAR(TO_TIMESTAMP(NEW.date_ready), 'DD.MM.YYYY');
	order_note := NEW.note;

	mail_subject := REPLACE(mail_subject, '[order_orderer]', order_orderer);
	mail_subject := REPLACE(mail_subject, '[order_date_ready]', order_date_ready);
	mail_body := REPLACE(mail_body, '[order_orderer]', order_orderer);
	mail_body := REPLACE(mail_body, '[order_date_ready]', order_date_ready);
	mail_body := REPLACE(mail_body, '[order_note]', order_note);
	PERFORM instance.mail_send(mail_subject,mail_body,mail_recipient);

	RETURN NEW;
END;
$BODY$

Dabei war es für mich super hilfreich, schnell sehen zu können, ob Daten (hier NEW.note) richtig extrahiert wurden:

	PERFORM instance.log_warning(NEW.note, 'kus_physlab');

Florian

Zwei Probleme habe ich aber noch.

1) Ich schaffe es nicht, die Namen aller Experimente, welche in der neuen Bestellung angegeben wurden, auszulesen und anzeigen zu lassen. Mein Ansatz ist:

	SELECT e.name
		INTO order_experiments
	FROM kus_physlab.order_experiment oe
		JOIN kus_physlab.experiment e ON e.id = oe.experiment_id
	WHERE oe.order_id = NEW.id;

	mail_body := 'order_experiments: ' || order_experiments;
	PERFORM instance.log_warning(mail_body, 'kus_physlab');

Wenn ich dies ausprobiere, erhalte ich die Fehlermeldung Spalte "message" of relation "log" beinhaltet keinen Wert oder kann nicht aufgelöst werden.
a) Was muss ich machen, damit die Namen angezeigt werden, also z.B. als kommaseparierte Liste oder mit Zeilenumbrüchen dazwischen?
b) Gibt es eine Lösung, die robust ist für den Fall, dass der Bestellung bisher keine Experimente hinzugefügt wurden?

2) Es klappt nicht, Feldwerte in der Backend Funktion zu setzen. Mit diesen Zeilen

	NEW.test := 'Test-Text';
	PERFORM instance.log_warning(NEW.test, 'kus_physlab');

Wird im Log zwar der Test-Text angezeigt, aber nicht im Formular in der App.

Habt ihr vielleicht noch eine Idee, was ich falsch mache?

gabriel

Hallo Florian,

zu deinen Themen:

1) s. a) weiter unten.

a) Dein SELECT hier kriegt im Zweifel mehr als eine Datenzeile zurück. Wenn du mehrere Datenzeilen zu einen einzelnen Wert zusammenbauen willst, musst du aggregieren. Das wäre bspw. sowas: SELECT STRING_AGG(e.name,', ') INTO order_experiments. Dann erhältst du eine Komma-separierte Liste an Werten als einen TEXT-Wert in deiner Variable. Das wird auch der Grund sein, warum du beim Loggen einen Fehler bekommst - mail_body war mutmaßlich NULL.

b) Hierzu machst du eine Abfrage gegen NULL, wie z. B.:
IF order_experiments IS NULL THEN RETURN NEW; END IF;

Wenn eine Aggregation keinen Input erhält, ergibt das Ergebnis NULL. Das speicherst du in order_experiments und damit kannst du auch prüfen, ob was zurück gekommen ist.

2) Da muss ich grade raten, aber kann es sein, dass du einen AFTER-Trigger nutzt?

Der BEFORE-Trigger gibt dir eine Zeile (row) zum Bearbeiten und speichert dann diese als NEW ab. Ein AFTER-Trigger feuert, nachdem die Zeile bereits in die Datenbank geschrieben worden ist - d. h. Änderungen an NEWwerden ignoriert.

Wir bieten bei Interesse auch Crash-Kurse an, wenn du mit REI3 in Zukunft mehr machen möchtest. Spart oft sehr viel Zeit - es ist natürlich auch ok, wenn du dich selbst reinfuchsen willst 😉

Florian

2) Na klar, du hast komplett recht. Ich weiss nicht mehr, weshalb ich beim Einrichten des Triggers von den Standardeinstellungen abgewichen bin, ohne genau zu wissen, wofür sie gut sind. Aber wie so oft in solchen Fällen ist es am Ende ärgerlich, wie viel Zeit deshalb hinter raus draufgeht...

1) Kann es sein, dass ich auf diese Weise gar nicht an Daten der n:m-Beziehung kommen kann? Ich habe den Verdacht, dass die Zeilen in order_experiment erst geschrieben werden, nachdem die neue Zeile in order geschrieben und die Triggerfunktion ausgeführt wurde.

Mit dem folgenden Code

	SELECT STRING_AGG(oe.id::TEXT,', ')
		INTO oe_id
	FROM kus_physlab.order_experiment oe
	WHERE oe.order_id = NEW.id;

	IF oe_id IS NULL THEN
		oe_id := 'NULL';
	END IF;

	temp_text := 'oe_id: ' || oe_id;
	PERFORM instance.log_warning(temp_text, 'kus_physlab');

bekomme ich jedenfalls auch immer NULL ausgegeben.

gabriel

Florian
Um dazu was sagen zu können, müsste ich genau wissen, welche Relationen du hast und wie deine Formulare aussehen.

Wenn du bspw. auf einem Formular mehrere Relationen per Join verbunden hast und dann einen neuen Datensatz erstellst, wird zuerst der Datensatz der Relation 0 angelegt (was den Trigger für diese Relation auslöst) und danach die Datensätze der anderen Relationen. Muss auch so sein, weil ansonsten die Referenz auf den ursprünglichen Datensatz nicht möglich ist.

Für genau diesen Fall gibt es ironischerweise den AFTER-Trigger mit der DEFERRED Option. Wenn DEFERRED aktiv ist, wird ein Trigger erst dann ausgeführt, wenn alle Veränderungen durch sind. Das würde dieses Problem lösen - mit einem AFTER-Trigger kannst du aber keine Änderungen an NEW vornehmen. Es hält dich aber keiner davon ab, per UPDATE-Statement diese Änderungen durchzuführen.

Florian

Juhu, es funktioniert alles! Sensationell!

Jetzt habe ich also vermutlich alle ungeeigneten Einstellungen für Trigger-Funktionen mal durchprobiert und trotz richtiger Ideen für die Datenbankabfragen häufig Fehler produziert. Solltet ihr mal ein Tutorial zu diesem Thema machen, wisst ihr jetzt immerhin, was ein Anfänger alles nicht weiss und wo sich Hinweise lohnen. 😉 Als Lehrer weiss ich, dass es manchmal nicht so leicht ist, sich in die Gedanken der Schüler hineinzuversetzen...

gabriel

Florian
Der Löwenanteil von REI3-Authoren nutzt auch keine Trigger. Es ist eher eine Expertenfunktion aktuell. Aber du hast Recht, das ist eines von den Themen, wo Schulungsmaterialen auf jeden Fall hilfreich wären. Die Liste an Themen für die nächsten Youtube-Videos wächst an 🙂