Dans le cadre d’un projet de collecte de données en ligne, nous souhaitions mettre en place depuis longtemps un système d’envoi de mail qui prévient à intervalle régulier les contributeurs, que leurs données ont été examinnée par un « expert » et qu’elles posent question.

L’idée générale était de mettre en place un trigger qui se déclenche aprés chaque modification du statut de validation d’une observation mais cela peut générer beaucoup de messages en cas de « validation » par lot.

Une vue sollicitée à intervalle régulier par une tâche cron sera donc plus appropriée.

pgmail se chargera de l’envoi des mails. C’est une fonction écrite en tcl qui a été créée pour envoyer des mails depuis la base de données.

L’installation de pgmail nécessite l’extension pltclu (u pour untrusted, c’est à dire que les fonctions sytèmes sont accessibles au language.

CREATE EXTENSION pltclu;

On installe ensuite la fonction pgmail dans le schéma qui nous convient le mieux :

CREATE OR REPLACE FUNCTION outils.pgmail(text, text, text, text)
RETURNS integer AS
[…]

Il ne reste plus qu’à ècrire le mail.

On souhaite envoyer un mail et un seul à chaque contributeur pour l’ensemble des données dont le statut de validation à changé.

Nous avons pour cela une table de suivi qui enregistre toutes les versions de chacun des tuples de la table de saisie ainsi que la date de la modification, son auteur et l’opération effectuée (INSERT, UPDATE, DELETE). Cette table et les triggers qui l’alimentent sont directement issus des exemples de la documentation de postgreSQL : http://docs.postgresql.fr/9.2/plpgsql-trigger.html#plpgsql-trigger-audit-example

Nous allons nous intéresser dans notre cas aux colonnes date_operationn validateur, statut_validation et decision_validation

L’idée générale est de detecter les lignes modifiées par un validateur (utilisateur = validateur) à la date du jour et d’en extraire la dernière valeur saisie pour les colonnes validation et decision_validation.

Les fonctions de fenêtre lead et first_value vont nous permettre cela :

http://docs.postgresql.fr/9.2/functions-window.html

La fonction d’aggrégation string_agg, d’aggréger les différentes lignes de résultat :

http://docs.postgresql.fr/9.2/functions-aggregate.html

Il y a probablement plus simple ou efficace comme requête mais celle-ci est une bonne première version fonctionnelle.

A reprendre avec les fonction window !