PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 14/11/2022 18:27:38

sab31
Membre

Nom colonne dans fonction de suivi de modification de table

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

#2 15/11/2022 05:40:53

rjuju
Administrateur

Re : Nom colonne dans fonction de suivi de modification de table

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é.

Hors ligne

#3 15/11/2022 14:16:33

dverite
Membre

Re : Nom colonne dans fonction de suivi de modification de table

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

Hors ligne

#4 16/11/2022 10:57:10

sab31
Membre

Re : Nom colonne dans fonction de suivi de modification de table

Bonjour,

merci pour vos réponses je vais tester vos solutions.

Hors ligne

#5 14/12/2022 13:24:00

sab31
Membre

Re : Nom colonne dans fonction de suivi de modification de table

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

Pied de page des forums