SELECT Trigger in postgres

Normalerweise gibt es nur Trigger um auf die schreibenden Kommandos zu reagieren. Was aber wenn man auch etwas auslösen möchte, wenn ein Datensatz gelesen wird?
Nehmen wir an wir haben ein Tabelle informationtext mit Informationstexten, wobei jeder über eine Eindeutige id verfügt.

Dann habe ich eine zweite Tabelle, wo pro Benutzer der Datenbank (über die userid des Users) eine Nachricht (id_information) verknüpft werden kann, jeder Datensatz verfügt wiederum auch über eine Eindeutige id. Die Felder read und suppressed sind per DEFAULT NULL und bekommen erst später Werte zugewiesen.


CREATE TABLE private.userinformations
(
id serial NOT NULL,
userid oid NOT NULL,
id_information integer NOT NULL,
read timestamp without time zone,
supressed timestamp without time zone,
CONSTRAINT pkey_id_userinformations PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);

Als Erstes benötigen wir eine Funktion pg_update_by_read(integer). In dieser Funktion schreiben wir quasi fest was zu tun ist, dabei übergeben wir einfach eine id als Parameter. Die Funktion selber aktualisiert ein Feld mit dem Namen read mit dem aktuellen Zeitstempel. Als nächstes kommt nur noch ein Rückgabewert für die Funktion in diesem Fall z.B. ‘neu’.

CREATE OR REPLACE FUNCTION private.pg_update_by_read(integer)
RETURNS text AS
$BODY$
UPDATE private.userinformations SET read = current_timestamp WHERE id = $1;
SELECT 'neu'::text;
$BODY$
LANGUAGE sql VOLATILE
COST 100;

Wichtig ist das die Funktion als VOLATILE angelegt wird, damit auch Daten geändert werden können.

Die Abfrage in welche man nun diese Art Trigger einbinden möchte sieht wie folgt aus (ich habe es in eine View getan):

CREATE OR REPLACE VIEW private.view_opennet_informations AS
SELECT ui.id, it.informationtext,
CASE
WHEN ui.read IS NULL THEN private.pg_update_by_read(ui.id)
WHEN ui.read IS NOT NULL THEN 'alt'::text
ELSE NULL::text
END AS age
FROM private.informationtext it, private.userinformations ui
WHERE it.id = ui.id_information AND ui.userid = private.pg_get_rolebyname("current_user"())::oid AND ui.suppressed IS NULL;

Die Ansicht holt alle Datensätze, welche dem aktuellen Benutzer (userid = private.pg_get_rolebyname(“current_user”())::oid ) zugeordnet wurden und nicht unterdrückt (suppressed IS NULL) wurden. Dabei verwende ich noch eine Funktion, welche anhand des aktuellen Benutzers die oid holt.

CREATE OR REPLACE FUNCTION private.pg_get_rolebyname(name)
RETURNS integer AS
'SELECT oid::INTEGER FROM pg_roles WHERE rolname = $1;'
LANGUAGE sql VOLATILE
COST 100;

Die Abfrage prüft im CASE/END also ob die Variable read den Wert NULL hat, wenn ja wird die Funktion private.pg_update_by_read(ui.id)
getriggert, anderen Falls wird nur der Wert ‘alt’ ausgegeben. Somit wird also das Feld read verwendet, um zu erkennen, dass der Datensatz vom Benutzer gelesen wurde. Achtung das Funktioniert nur, weil der Datensatz exklusiv dem Benutzer zugeordnet wurde.

Um eine Nachricht nicht mehr anzuzeigen reicht ein:
UPDATE private.userinformations SET suppressed = current_timestamp WHERE id = $1; mit der entsprechenden id.