Vous n'êtes pas identifié(e).
Bonjour à tous,
J'ai créé une table de suivi 'suivi_modif_tables' dans un schéma 'test_suivi' pour suivre les modifications de plusieurs autres tables lors d'INSERT, UPDATE et DELETE.
J'ai créé la fonction ci-dessous qui fonctionne très bien et j'ai créé le trigger également sans souci.
Le problème se pose quand j'ai voulu ajouté le champ 'nom_colonne_modif' dans la table de suivi et dans la fonction car j'aimerais avoir le nom de la colonne qui a été modifiée lors de la maj.
Du coup j'ai des messages d'erreur car il ne trouve pas la colonne 'column_name'
Est-ce que quelqu'un a une idée pour compléter ma fonction ?
Merci d'avance de votre aide
CREATE OR REPLACE FUNCTION test_suivi_tables.fn_suivi_modif_tables()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
identifiant TEXT;
BEGIN
IF (TG_OP = 'UPDATE') THEN
identifiant := OLD.id_tableA;
INSERT INTO test_suivi_tables.suivi_modif_tables (
nom_schema ,
nom_table ,
utilisateur ,
action ,
id_objet_modifie ,
date_derniere_modif,
nom_colonne_modif
)
VALUES (
TG_TABLE_SCHEMA::TEXT,
TG_TABLE_NAME::TEXT,
session_user::TEXT,
substring(TG_OP,1,1),
identifiant,
now(),
column_name
);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
identifiant := OLD.id_tableA;
INSERT INTO test_suivi_tables.suivi_modif_tables (
nom_schema ,
nom_table ,
utilisateur ,
action ,
id_objet_modifie,
date_derniere_modif,
nom_colonne_modif
)
VALUES (
TG_TABLE_SCHEMA::TEXT,
TG_TABLE_NAME::TEXT,
session_user::TEXT,
substring(TG_OP,1,1),
identifiant,
now(),
column_name
);
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
identifiant := NEW.id_tableA;
INSERT INTO test_suivi_tables.suivi_modif_tables (
nom_schema ,
nom_table ,
utilisateur ,
action ,
id_objet_modifie,
date_derniere_modif,
nom_colonne_modif
)
VALUES (
TG_TABLE_SCHEMA::TEXT,
TG_TABLE_NAME::TEXT,
session_user::TEXT,
substring(TG_OP,1,1),
identifiant,
now(),
column_name
);
RETURN NEW;
ELSE
RAISE WARNING '[test_suivi_tables.fn_suivi_modif_tables] - Other action occurred: %, at %', TG_OP,now();
RETURN NULL;
END IF;
END;
$BODY$
Hors ligne
Bonjour,
Il n'y a effectivement pas de column_name disponible. Il n'y a d'ailleurs aucune garantie qu'une seule colonne a été modifiée lors d'un UPDATE. Vous devez comparer OLD.colonne avec NEW.colonne pour chacune des colonnes de la table si vous souhaitez détecter ce qui a changé.
Julien.
https://rjuju.github.io/
Hors ligne
Pour avoir la liste des colonnes changées quelque soit la structure de la table, on peut utiliser une requête de ce style, qui fait l'intersection de OLD et NEW pour ne garder que ce qui change:
select array_agg(k2)
from (select * from jsonb_each_text(row_to_json(OLD)::jsonb)) as r1(k1,v1)
join (select * from jsonb_each_text(row_to_json(NEW)::jsonb)) as r2(k2,v2)
on k1=k2
where v1 is distinct from v2;Il y a une version avec hstore aussi, où on peut faire encore plus simplement
hstore(NEW.*) - hstore(OLD.*)Mais hstore étant rendu obsolète par jsonb, ce n'est pas forcément une bonne idée de partir là dessus aujourd'hui.
La version "toutes options" du trigger d'audit avec hstore est maintenue ici:
https://github.com/2ndQuadrant/audit-tr … /audit.sql
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Bonjour,
merci pour vos réponses je vais tester vos solutions.
Hors ligne
Bonjour, merci Daniel pour ta solution JSONB, j'ai chercher un moment comment l'utiliser car je ne maîtrise pas encore le JSON mais je suis ravie du résultat.
J'ai cherché compliqué alors qu'il fallait l'utiliser comme elle était mais ça m'a fait découvrir ce type de données que je trouve très pratique.
Du coup voici ma fonction modifiée :
BEGIN
IF (TG_OP = 'UPDATE') THEN
identifiant := OLD.id_tableA;
INSERT INTO test_suivi_tables.suivi_modif_tables (
nom_schema ,
nom_table ,
utilisateur ,
action ,
id_objet_modifie ,
date_derniere_modif,
colonne_modif
)
VALUES (
TG_TABLE_SCHEMA::TEXT,
TG_TABLE_NAME::TEXT,
session_user::TEXT,
substring(TG_OP,1,1),
identifiant,
now(),
(( select (array_agg(k2)) nom_colonne
from (select * from jsonb_each_text(row_to_json(OLD)::jsonb)) as r1(k1,v1)
join (select * from jsonb_each_text(row_to_json(NEW)::jsonb)) as r2(k2,v2)
on k1=k2
where v1 is distinct from v2)::TEXT)
);
-- pour enlever les parenthèses qui restent après changement de format de type JSON en TEXT
update tables_de_suivi.suivi_modif_tables
set colonne_modif = trim(both '{}' FROM "colonne_modif");
);J'ai effectué le test avec plusieurs champs modifiés sur la même ligne, je récupère bien tous les noms des champs modifiés séparés par une virgule et sans les parenthèses
Hors ligne