Forums PostgreSQL.fr

ced

Membre

Transformer en XML le contenu de OLD.* dans un trigger

Bonjour,

Je tente de mettre en place un système d'audit des tables d'une base de données, via un système de triggers en PL/pgSQL sous PostgreSQL 8.4.

J'en suis à tenter d'insérer les anciennes données (avant mise à jour ou suppression) dans une table d'audit.
Pour m'en sortir, j'ai choisi une solution qui consiste à transformer en XML tout le contenu de la ligne avant changement, et stocker ça dans un champ de type xml.

Seulement, impossible de trouver comment transformer le contenu du record OLD en XML.

Voici ce que j'essaie :

CREATE OR REPLACE FUNCTION test.audit_delete_offline () RETURNS TRIGGER AS
$body$
BEGIN
 
INSERT INTO audit.data_change (tbl_id, chg_user, chg_datetime, chg_delete, chg_xml_data)
SELECT lt.tbl_id, CURRENT_USER, CURRENT_TIMESTAMP, 1, XMLFOREST(OLD.*)
FROM audit.liste_tables lt
WHERE lt.tbl_schema = TG_TABLE_SCHEMA
AND lt.tbl_name = TG_TABLE_NAME;
RETURN NULL;
 
END;
$body$
LANGUAGE plpgsql;

J'ai stocké dans une table (audit.liste_tables) toutes les tables sur lesquelles l'enregistrement des modifications doit s'enregistrer.

Je crée ensuite mon trigger sur ma table à auditer.

Quand je supprime un enregistrement, j'ai l'erreur suivante :

ERROR: row expansion via "*" is not supported here
LINE 1: ...d, CURRENT_USER, CURRENT_TIMESTAMP, 0, XMLFOREST( $1 .*) FRO...

J'ai tenté plusieurs choses, notamment en essayant d'utiliser la fonction QUERY_TO_XML, mais rien n'y fait... Je ne trouve pas de solution.

D'où ma question : comment est-ce que je peux transformer en XML tout le contenu de la variable OLD dans un trigger ?

Merci d'avance à ceux qui voudront bien m'aider...

ced

gleu

Administrateur

Re : Transformer en XML le contenu de OLD.* dans un trigger

À mon avis, une solution qui pourrait fonctionner passerait par cursor_to_xml : création d'un curseur sur OLD, lecture du curseur via cursor_to_xml, concaténation de la valeur de cursor_to_xml dans une variable texte, et à la fin du parcours du curseur, insertion dans la table audit de la variable texte.

Cela étant dit, pourquoi ne pas utiliser les modules d'audit qui existent déjà comme tablelog ou emaj.

---

Guillaume.

ced

Membre

Re : Transformer en XML le contenu de OLD.* dans un trigger

C'est une excellente idée. J'ai finalement opté pour la solution avec table_to_xml en passant par une table temporaire.
Voici ce que ça donne :

CREATE OR REPLACE FUNCTION test.audit_update_offline () RETURNS TRIGGER AS
$body$
BEGIN

CREATE TEMP TABLE tblchg AS SELECT OLD.*;

INSERT INTO audit.data_change (tbl_id, chg_user, chg_datetime, chg_delete, chg_xml_data)
SELECT lt.tbl_id, CURRENT_USER, CURRENT_TIMESTAMP, 0, (SELECT * FROM TABLE_TO_XML('tblchg', false, false, ''))
FROM audit.liste_tables lt
WHERE lt.tbl_schema = TG_TABLE_SCHEMA
AND lt.tbl_name = TG_TABLE_NAME;

DROP TABLE tblchg;

RETURN NULL;

END;
$body$
LANGUAGE plpgsql;

Pour info, j'ai choisi de ne pas utiliser les solutions déjà existantes parce qu'elles ont le défaut de créer une table d'audit par table auditée. Or, dans mon cas, je préfère une seule table d'audit.

Encore merci de ton aide,

ced

Dernière modification par ced (18/05/2010 14:12:04)