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

Pied de page des forums