Forums PostgreSQL.fr

tony emery

Membre

fonction avec récupération current_user et current timestamp

Bonjour à tous,

J'utilise postgresql depuis quelques mois dans le cadre de mon travail (collectivité territoriale). Je vous explique le contexte.
J'ai une table contenant des colonnes de collecte de déchets dans laquelle se trouvent des informations sur l'emplacement, la gestion, le type et le remplissage de la colonne.
Cette table est diffusée via un websig aux agents de la collectivité qui sont amenés à en modifier les informations.

Je souhaite suivre les modifications de cette table et, pour cela, j'ai créé deux fonctions :
- une dans le cas où l'agent met à jour le taux de remplissage de la colonne => les informations modifiées sont copiées dans une nouvelle table de suivi ;
- une dans le cas où l'agent modifie l'emplacement ou la nature de la colonne (remplacement ou nouvelle implantation par exemple) => la synthèse de la modification est copiée dans une nouvelle table des mises à jour.

Ces 2 fonctions fonctionnent bien et les tables se remplissent correctement.
Cependant, je voudrais que, dans la table des colonnes, certains champs se mettent à jour de manière automatique.
J'ai commencé par 2 informations que je pensais facile à récupérer : le nom de l'agent qui a fait la modification (current_user) et la date de la modification (current_timestamp).

L'une des fonctions est la suivante (la deuxième étant presque identique, je vous fais grâce d'un peu de lecture) :

CREATE FUNCTION s_environnement.fonction_maj_colonnes()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF SECURITY DEFINER
    SET search_path=pg_catalog, s_environnement
AS $BODY$DECLARE
variable_ancienne_valeur TEXT;
variable_nouvelle_valeur TEXT;
identifiant INTEGER;
	BEGIN
		IF (TG_OP = 'UPDATE') THEN
			variable_ancienne_valeur := ROW(OLD.*);
			variable_nouvelle_valeur := ROW(NEW.*);
			identifiant := OLD.id_serial;
			INSERT INTO s_environnement.suivi (schema, nomtable, utilisateur, dateheure, action, dataorigine, datanouvelle, detailmaj, idobjet)
			VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user, current_timestamp, substring(TG_OP,1,1), variable_ancienne_valeur, variable_nouvelle_valeur, current_query(), identifiant);
        	NEW.date_valid := current_timestamp;
        	NEW.modified_by := current_user;
			RETURN NEW;
		ELSIF (TG_OP = 'DELETE') THEN
			variable_ancienne_valeur := ROW(OLD.*);
			identifiant := OLD.id_serial;
			INSERT INTO s_environnement.suivi (schema, nomtable, utilisateur, dateheure, action, dataorigine, detailmaj, idobjet)
			VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user, current_timestamp, substring(TG_OP,1,1), variable_ancienne_valeur, current_query(), identifiant);
			RETURN OLD;
		ELSIF (TG_OP = 'INSERT') THEN
			variable_nouvelle_valeur := ROW(NEW.*);
			identifiant := NEW.id_serial;
			INSERT INTO s_environnement.suivi (schema, nomtable, utilisateur, dateheure, action, datanouvelle, detailmaj, idobjet)
			VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, session_user, current_timestamp, substring(TG_OP,1,1), variable_nouvelle_valeur, current_query(), identifiant);
        	NEW.date_valid := current_timestamp;       -- <---- C'EST LA QUE J'AI UN PROBLEME
        	NEW.modified_by := current_user;       -- <---- C'EST LA QUE J'AI UN PROBLEME
			RETURN NEW;
		ELSE
			RAISE WARNING '[s_environnement.fonction_suivi_maj] - Other action occurred: %, at %', TG_OP,now();
			RETURN NULL;
		END IF;
	END;
$BODY$;

Bizarrement, toute la partie où j'écris les modifications dans la nouvelle table fonctionne (INSERT INTO ...). Par contre, les 2 lignes suivantes me posent problème :
- NEW.date_valid := current_timestamp;
- NEW.modified_by := current_user;
Ces deux lignes sont censées écrire dans la table d'origine (celle des colonnes) mais, en fait, il ne se passe rien. Je n'ai même pas de message d'erreur.

Voilà, si l'un de vous a une piste de réflexion, ça fait quelques jours que je m'arrache les cheveux pour savoir d'où vient le problème.

Merci d'avance,

Marc Cousin

Membre

Re : fonction avec récupération current_user et current timestamp

L'explication la plus simple que je vois: vous avez déclaré le trigger comme "AFTER". Si ce n'était pas le cas vous auriez des erreurs sur les INSERT. Par contre, pour pouvoir modifier NEW, il faut un trigger BEFORE. Donc je pense que vu ce que vous voulez faire, c'est un trigger BEFORE qui modifie NEW, et un trigger AFTER qui fait les insert.

---

Marc.

tony emery

Membre

Re : fonction avec récupération current_user et current timestamp

Merci pour l'information.
Du coup, dois-je faire 2 fonctions (une BEFORE et une AFTER) ou puis-je mettre ces 2 actions dans la même fonction ?
Existe-t-il une documentation synthétique sur le rôle BEFORE et AFTER pour savoir ce que l'on peut faire (et ne pas faire) ?

Marc Cousin

Membre

Re : fonction avec récupération current_user et current timestamp

Il faut deux fonctions, appelées par deux triggers différents.

La doc est là : https://www.postgresql.org/docs/10/trig … ition.html . Ce n'est pas extrêmement synthétique, mais ce n'est pas un sujet très simple non plus

---

Marc.

tony emery

Membre

Re : fonction avec récupération current_user et current timestamp

Merci pour la documentation.
Et je confirme que ce n'est pas simple : j'ai vidé ma tablette de Paracétamol...

tony emery

Membre

Re : fonction avec récupération current_user et current timestamp

J'ai pu faire mon deuxième trigger qui est, du reste, beaucoup plus simple que les 2 premiers.
J'ai donc mon trigger :

CREATE TRIGGER trigger_horodatage
    BEFORE INSERT OR UPDATE 
    ON s_environnement.env_colonnes
    FOR EACH ROW
    EXECUTE PROCEDURE s_environnement.fonction_horodatage();

et ma fonction :

DECLARE
	BEGIN
        NEW.date_valid := current_timestamp;
        NEW.modified_by := session_user;
        NEW.coord_x := ST_X(NEW.geom);
        NEW.coord_y := ST_Y(NEW.geom);
		RETURN NEW;
	END;

La partie current_timestamp et session_user fonctionne bien (pour le moment), mais la partie où je récupère les coordonnées x et y dans ma table me renvoi l'erreur suivante :

Impossible de valider les changements pour la couche env_colonnes
Erreurs : ERREUR : 1 géométrie non modifiée.
Erreur du fournisseur de données :
      Erreur PostGIS lors de la modification de géométrie : ERREUR:  l'enregistrement « new » n'a pas de champs « geom »
    CONTEXT:  instruction SQL « SELECT st_x(NEW.geom) »
    fonction PL/pgSQL fonction_horodatage(), ligne 5 à affectation

Pourtant, j'ai regardé sur Internet et la fonction est bien écrite comme ça. De plus, ma colonne géométrique s'appelle bien geom.

rjuju

Administrateur

Re : fonction avec récupération current_user et current timestamp

Aucun problème de mon coté :

=# create table test(geom geometry, date_valid timestamptz, modified_by text, coord_x double precision, coord_y double precision);
CREATE TABLE

=# create function horo() returns trigger as
$$
DECLARE
        BEGIN
        NEW.date_valid := current_timestamp;
        NEW.modified_by := session_user;
        NEW.coord_x := ST_X(NEW.geom);
        NEW.coord_y := ST_Y(NEW.geom);
                RETURN NEW;
        END;
$$ language plpgsql;
CREATE FUNCTION

# CREATE TRIGGER trigger_horodatage
    BEFORE INSERT OR UPDATE
    ON test           
    FOR EACH ROW
    EXECUTE PROCEDURE horo();
CREATE TRIGGER

=# insert into test (geom) select 'POINT(0 0 0 0)'::geometry;
INSERT 0 1

=# table test;
                                    geom                                    |          date_valid           | modified_by | coord_x | coord_y 
----------------------------------------------------------------------------+-------------------------------+-------------+---------+---------
 01010000C00000000000000000000000000000000000000000000000000000000000000000 | 2020-01-24 15:14:13.440822+01 | rjuju       |       0 |       0
(1 row)

Sans plus de détails, difficile de dire ce qui bloque chez vous.  J'imagine un mauvais schéma ou un nom de colonne en majuscule ?

---

Julien.
https://rjuju.github.io/

tony emery

Membre

Re : fonction avec récupération current_user et current timestamp

Ah, effectivement, la piste du schéma peut être bonne car, dans le message d'erreur, j'ai : "Erreur PostGIS lors de la modification de géométrie : ERREUR:  la fonction st_x(public.geometry) n'existe pas".
Or, ma table "env_colonnes" se trouve dans le schema "s_environnement". Ma colonne de géométrie s'appelle bien "geom" et les champs des coordonnées "coord_x" et "coord_y" sans majuscule...

En testant votre solution, ça fonctionne aussi de mon côté. La seule différence est que, dans votre exemple, la table se trouve dans le schéma "public".
Ensuite, les champs sont légèrement différents :
- mon champ "geom" est en "geometry(Geometry,2154)" alors que le vôtre est en "geometry" ;
- mes champs "coord_x" et "coord_y" sont en "real" alors que les vôtres sont en "double precision" ;

Dernière modification par tony emery (24/01/2020 17:10:51)

rjuju

Administrateur

Re : fonction avec récupération current_user et current timestamp

Le public ici correspond à priori au schéma d'installation de l'extension postgis.  Le message que vous indiquez ici est liée à une autre erreur ?  Il serait beaucoup plus simple si vous pouviez fournir la totalité des messages d'erreurs, requêtes, fonctions etc (idéalement un moyen de reproduire l'erreur en partant de zéro).

---

Julien.
https://rjuju.github.io/

tony emery

Membre

Re : fonction avec récupération current_user et current timestamp

**Je crois que j'ai trouvé**
J'ai supprimé le "SET search_path=pg_catalog, s_environnement" qui se trouvait dans ma fonction (cf mon tout premier post).
A priori, ça fonctionne.
Bon, j'ai d'autres erreurs qui apparaissent mais celle-là, elle est réglée...