Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
Migrant notre SGBDR de Oracle vers Postgre, je fais appel à votre savoir devant une problématique que je n'arrive pas à résoudre.
Nous historisons toutes nos tables avec une procédure de génération de triggers on delete qui copie l'enregistrement supprimé dans la table d'historisation (table_origine||'_histo'). Je ne connais ni l'ensemble des tables, ni l'ensemble des colonnes associées à chaque table.
La procédure cherche donc la liste des tables, puis pour chaque table une boucle sur la table système des colonnes, qui construit le script a exécuter pour créer les triggers nécessaires sur chaque table.
Voici mon script, qui fonctionne bien sous oracle.
CREATE OR REPLACE PROCEDURE ADM."CREATE_TRIG_D"
as
---------------------------------------------------------------------------------------------
-- Fonction permettant de créer les triggers d'historisation
-- Sur delete d'un enregistrement dans une table utilisateur
---------------------------------------------------------------------------------------------
col_exist exception;
pragma exception_init(col_exist,-01430);
sqltxt varchar2(3000);
nb number(5);
nomcol1 varchar2(3000);
nomcol2 varchar2(3000);
cursor c_object is
select table_name
from user_tables
where TABLE_NAME not like '%HISTO';
V_object c_object%rowtype;
begin
for v_object in c_object
loop
nb := 0;
for vcol in (select column_name from user_tab_columns where table_name=v_object.table_name) loop
nb := nb+1;
if nb = 1 then
nomcol2 := ':old.'||vcol.column_name;
nomcol1 := vcol.column_name;
else
nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
nomcol1 := nomcol1 ||','||vcol.column_name;
end if;
end loop;
begin
sqltxt := 'CREATE OR REPLACE TRIGGER ADM.TR_'||v_object.table_name ||'_D after delete on SIG.'||v_object.table_name ||' for each row begin insert into ADM.'||v_object.table_name ||'_HISTO ('||nomcol1||') values ('||nomcol2||'); end;';
execute immediate sqltxt;
end;
end loop;
end;
Je n'arrive pas à reproduire le principe sous postgre, de manière à avoir une fonction trigger générique sur laquelle s'appuieraient les triggers on delete.
Voici un bout de code sur lequel je suis pour l'instant, mais selon la methodo, soit les variables ne sont pas reconnues, soit les 'old. ou new.' ne sont pas reconnues (en EXECUTE par exemple)
CREATE OR REPLACE FUNCTION public.ftr_maj_tabhisto()
RETURNS trigger AS
$BODY$DECLARE
nb integer;
vcol record;
nomcol1 varchar;
nomcol2 varchar;
BEGIN
nb := 0;
for vcol in (select column_name FROM information_schema.columns where table_name=TG_TABLE_NAME) loop
nb := nb+1;
if nb = 1 then
nomcol2 := ':old.'||vcol.column_name;
nomcol1 := vcol.column_name;
else
nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
nomcol1 := nomcol1 ||','||vcol.column_name;
end if;
end loop;
EXECUTE 'insert into '||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_HISTO ('||nomcol1||') values ('||nomcol2||')';
RETURN OLD;
END;
$BODY$
Merci d'avance de votre aide et bonnes fetes de fin d'année
Guillaume
Hors ligne
Votre fonction PL/SQL (oracle donc) ajoute des triggers. Votre fonction PL/pgsql (donc postgresql) insère des lignes dans une table. Difficile de comprendre ce que vous cherchez réellement à faire.
Le mieux est de donner votre fonction, et l'erreur que cela génère. Ensuite, nous, on peut expliquer l'erreur et éventuellement proposer une correction. Vous essayez et en cas d'erreur, on recommence.
Guillaume.
Hors ligne
Oui, puisque sous Oracle la fonction est dans le trigger généré.
A priori sous postgresql le trigger (on delete dans mon cas) appelle une fonction qui doit donc être générique pour pouvoir etre utilisée sur plusieurs tables.
Ce que je cherche à faire : lorsqu'il y a une suppression d'un enregistrement dans la table toto, inscrire l'enregistrement dans la table toto_histo.
Et ce, pour un ensemble de tables.
J'ai donc besoin d'une fonction qui liste les colonnes de la table impactée par le trigger et qui insère les valeurs supprimées ("OLD") dans la table histo correspondant.
Voici la fonction que j'ai mis en place :
CREATE OR REPLACE FUNCTION public.ftr_maj_tabhisto()
RETURNS trigger AS
$BODY$DECLARE
nb integer;
vcol record;
nomcol1 varchar;
nomcol2 varchar;
BEGIN
nb := 0;
for vcol in (select column_name FROM information_schema.columns where table_name=TG_TABLE_NAME) loop
nb := nb+1;
if nb = 1 then
nomcol2 := ':old.'||vcol.column_name;
nomcol1 := vcol.column_name;
else
nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
nomcol1 := nomcol1 ||','||vcol.column_name;
end if;
end loop;
EXECUTE 'insert into '||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_HISTO ('||nomcol1||') values ('||nomcol2||')';
RETURN OLD;
END;
$BODY$Et voici l'erreur retournée à la suppression d'un enregistrement :
ERREUR: entrée manquante de la clause FROM pour la table « old »
LINE 1: ...,source_donnee,adresse,log_integ,log_maj) values (old.object...
^
QUERY: insert into administratif.erp_HISTO (objectid,erp,nom,code_insee_commune,nom_commune,date_integ,date_maj,shape,date_donnee,source_donnee,adresse,log_integ,log_maj) values (old.objectid,old.erp,old.nom,old.code_insee_commune,old.nom_commune,old.date_integ,old.date_maj,old.shape,old.date_donnee,old.source_donnee,old.adresse,old.log_integ,old.log_maj)
CONTEXT: PL/pgSQL function ftr_maj_tabhisto() line 27 at instruction EXECUTE
********** Erreur **********
ERREUR: entrée manquante de la clause FROM pour la table « old »
État SQL :42P01
Contexte : PL/pgSQL function ftr_maj_tabhisto() line 27 at instruction EXECUTEJe n'arrive pas à utiliser les valeurs OLD dans l'insertion à travers la commande EXECUTE.
Merci
Hors ligne
Les références OLD et NEW ne sont comprises que dans la fonction elle-même. Si vous placez le texte OLD.qqc dans une chaîne de caractère, c'est juste une chaîne de caractère sans signification particulière. Dans ce cas, il vaut mieux utiliser OLD.* après avoir rempli la liste des colonnes.
De plus, votre fonction présente de nombreux problèmes. Si une des colonnes vaut NULL, la requête à exécuter devient NULL, ce qui provoque une erreur. Si une des colonnes contient un texte qui contient une virgule, PostgreSQL aura plus de valeurs que de colonnes. Si jamais une des colonne contient un guillemet simple, la chaîne de caractères représentant la requête à exécuter s'arrêtera avant ce qui est prévu, provoquant d'autres erreurs. Bref, il faudrait songer à utiliser les fonctions quote_ident et quote_literal.
Guillaume.
Hors ligne
Sous postgre, je suis donc bloqué dans le traitement que je veux faire ?
En imaginant que les structures des tables d'origine et histo soient toujours identiques, je pourrais utiliser une fonction plus simple, sans boucle, comme celle ci :
$BODY$DECLARE
nom_tab VARCHAR;
BEGIN
nom_tab := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_histo';
EXECUTE 'INSERT INTO '||nom_tab||' SELECT '||OLD.*;
RETURN OLD;
END;
$BODY$Je suis alors obligé d'utiliser un EXECUTE pour pouvoir inscrire ma ligne dans une table histo dont le nom est dans une variable.
Mais sans construire la liste des colonnes à partir d'une boucle, je ne peux utiliser de fonction quote_ident sur des colonnes que je n'aurais pas identifié, comme le old.*
La "solution" est-elle de créer une fonction par table à historiser pour pouvoir mettre le nom de la table en dur et sortir de l'EXECUTE ?
C'est à dire 350 fois la même fonction pour seulement changer le nom de la table histo ?
Comme ceci :
$BODY$DECLARE
nom_tab VARCHAR;
BEGIN
-- nom_tab := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_histo';
INSERT INTO toto1_histo SELECT OLD.*;
RETURN OLD;
END;
$BODY$Dernière modification par guirom (26/12/2014 11:10:36)
Hors ligne
Sous postgre, je suis donc bloqué dans le traitement que je veux faire ?
Quelque soit le logiciel, on est forcément bloqué à un moment. Bref 
La documentation http://docs.postgresql.fr/9.4/plpgsql-trigger.html vous explique ce qu'il est possible de faire.
La "solution" est-elle de créer une fonction par table à historiser pour pouvoir mettre le nom de la table en dur et sortir de l'EXECUTE ?
Non. D'ailleurs, je ne vois pas pourquoi vous cherchez à créer votre propre fonction d'historisation alors qu'il en existe déjà. E-maj par exemple (http://pgxn.org/dist/e-maj/).
Guillaume.
Hors ligne
Bonjour Guirom.
J'ai créé une fonction trigger qui permet l'enregistrement des données modifiées dans uen table historique.
Pour cela j'ai commencé par créer la table dans laquelle sont enregistrés les éléments modifiés :
CREATE TABLE historique
(
ogc_fid serial NOT NULL,
table_modif character varying(120),
type_modif character varying(120),
ogc_fid_modif integer,
date_modif date,
heure_modif time without time zone,
original_data text,
new_data text,
CONSTRAINT historique_pkey PRIMARY KEY (ogc_fid )
)
WITH (
OIDS=FALSE
);
ALTER TABLE historique
OWNER TO postgres;Puis j'ai créé la fonction trigger :
CREATE OR REPLACE FUNCTION historique()
RETURNS trigger AS
$BODY$
DECLARE
old_data text;
data_modif text;
BEGIN
IF (tg_op = 'UPDATE')
THEN
old_data := ROW(OLD.*);
data_modif := ROW(NEW.*);
INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data)
VALUES (tg_table_name::text,'Update',NEW.ogc_fid,current_date,current_time,old_data,data_modif);
RETURN NEW;
ELSIF (tg_op = 'DELETE')
THEN
old_data := ROW(OLD.*);
data_modif := NULL;
INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data)
VALUES (tg_table_name::text,'Delete',OLD.ogc_fid,current_date,current_time,old_data,data_modif);
RETURN OLD;
ELSIF (tg_op = 'INSERT')
THEN
old_data := NULL;
data_modif := ROW(NEW.*);
INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data)
VALUES (tg_table_name::text,'Insert',NEW.ogc_fid,current_date,current_time,old_data,data_modif);
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;Et pour finir, j'applique à chaque table pour laquelle je souhaite enregistrer les modifications le lancement de la fonction trigger :
CREATE TRIGGER historique
BEFORE INSERT OR UPDATE OR DELETE
ON table
FOR EACH ROW
EXECUTE PROCEDURE historique();Mais peut-être existe-t-il des méthodes plus simple, que je ne connais pas et pour lesquelles je serais curieux de connaitre l'existence.
Geo-x
Hors ligne
Bonjour,
Je n'ai pas réussi à trouver la méthode pour historiser de manière spécifique chaque table de données. C'est à dire enregistrer les transactions dans une table historique de même structure que la table d'origine par une procédure (déclenchée par un trigger) qui lirait dans les tables de métadonnées la liste des colonnes de la table source pour archiver la transaction dans sa table d'historisation.
J'ai donc adopté la méthode que vous avez mise en place afin d'avoir au moins une vue générale, même s'il parait plus compliqué de restaurer des informations malencontreusement supprimées/modifiées par des erreurs de manipulation de nos utilisateurs SIG. Je l'ai adapté en isolant dans des champs particuliers, les informations géométriques, qui sont présentes dans toutes les tables.
Merci pour votre aide.
Guillaume
Hors ligne
Pages : 1