Forums PostgreSQL.fr

Mangone

Membre

Débutant demande aide

je cherche à  ajouter une "colonne date" qui renferme les dates de mise à jours. A chaque fois qu'une mise à jour sera effectuée, la date sera  saisie automatiquement dans cette colonne .
En somme toute les mises à jour sera visibles dans cette colonne
Pouvez-vous m'aider ?

gleu

Administrateur

Re : Débutant demande aide

Quel problème rencontrez-vous ?

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

Bonjour Gleu,

Merci d'avoir répondu à ma demande, merci aussi pour la disponibilité.
Voilà 4 nuit que j'arrache les cheveux sur un problème que je n'arrive pas à résoudre:
J'ai un table de 5 colonnes (4 en type integer) et la 5 ème une colonne (date en type date).
La 5 ème colonne enregistre la date de mise à jour. A chaque fois qu'il y a mise à jour la date et consignée sur cette colonne date.
J'ai pensé utiliser une fonction trigger et un trigger. Actuellement ma base n'a ni trigger ni fonction.
Le premier sera pour la colonne date et les 2 autres triggers "before insert et before update sur cette colonne pour affecter la date système.

Voilà l 'aide dont j'ai besoin, je vous serais reconnaissant de bien vouloir m'aider.
Merci d'avance

Bonne soirée

gleu

Administrateur

Re : Débutant demande aide

Il faut en effet ajouter une fonction trigger et un trigger qui va déclencher cette fonction. À défaut de questions concrètes, je peux vous donner des liens vers la documentation, notamment https://docs.postgresql.fr/10/triggers.html pour l'écriture de fonctions triggers, et https://docs.postgresql.fr/10/sql-createtrigger.html pour la création d'un trigger.

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

Bonjour,

J'ai suivi les liens, voilà ce que je vous présente et qui ne fonctionne pas pour le moment. Je ne sais si je m'y prends mal ou pas certainement oui . Merci de m'assister s'il vous plait

CREATE TRIGGER nom_du_trigger BEFORE UPDATE ON ma_table
FOR EACH ROW SET NEW.ma_5e_colonne = [date];

La question que je me pose excusé mon manque de culture !!!
1) Me faut il une fonction trigger avant de créer un trigger ?
je pose cette question parce que quand je vais sur trigger, je n'ai pas ou coller ce bout de code.

Dois-créer une nouvelle fonction trigger si oui comment la créer ?
Comment faire marcher ce bout de code .

Merci de bien vouloir me donner un coup de pouce s'il vous plait .

rjuju

Administrateur

Re : Débutant demande aide

Oui, vous devez créer la fonction avant de créer le trigger.  Vous avez un exemple complet ici : https://docs.postgresql.fr/10/plpgsql-trigger.html

---

Julien.
https://rjuju.github.io/

Mangone

Membre

Re : Débutant demande aide

Re à tous et toutes,

Merci pour les conseils.
Pouvez-vous me dire si cette fonction marchera avec mon trigger s'il vous plait ?
Si cela ne marche pas , que dois-je corriger ?
Merci d'avance pour votre aide .

Code

CREATE FUNCTION suivi_mise_a_jour() RETURNS TRIGGER AS $BODY$
        BEGIN
-- Insertion date de dernière mise à jour

IF NEW.date IS NULL THEN
RAISE EXCEPTION NEW.date ne peut pas être NULL ;
END IF ;
NEW.date:= current_timestamp ;
RETURNS NEW ;
END ;
$BODY$ LANGUAGE plpgsql

CREATE TRIGGER suivi_mise_a-jour BEFORE INSERT OR UPDATE ON ma_table
    FOR EACH ROW EXECUTE PROCEDURE suivi_mise_a_jour);

rjuju

Administrateur

Re : Débutant demande aide

À priori le message pour le RAISE EXCEPTION devrait être entouré de guillemets simples.  La vérification me semble cela dit bizarre, pourquoi demander qu'une valeur soit présente pour la forcer après ?

Sinon, pourquoi ne pas créer et essayer le trigger pour vérifier qu'il fonctionne ?

---

Julien.
https://rjuju.github.io/

Mangone

Membre

Re : Débutant demande aide

Merci Julien  pour le retour.
Je suis une vraie bille, c'est pourquoi je demande conseils aux expert.
Je ne connais pas postgresql voilà pourquoi je fais du bricolage et sollicite votre aide.

CREATE FUNCTION suivi_mise_a_jour() RETURNS TRIGGER AS $BODY$
        BEGIN
-- Insertion date de dernière mise à jour
IF NEW.date:= current_timestamp ;
RETURNS NEW ;
END IF;
$BODY$ LANGUAGE plpgsql

CREATE TRIGGER suivi_mise_a-jour BEFORE INSERT OR UPDATE ON ma_table
    FOR EACH ROW EXECUTE PROCEDURE suivi_mise_a_jour);

gleu

Administrateur

Re : Débutant demande aide

Être une vraie bille (vos termes) n'empêche pas de tester. Vous ne risquez pas de casser quoi que ce soit. Et en l'occurence, vous auriez vu une erreur. Le RETURNS dans le corps de la fonction doit être un RETURN.

Votre deuxième exemple n'a pas de sens. Vous ne pouvez pas faire une affectation ( := ) dans un test. Soit vous faites une affectation, soit vous faites un test. A priori, vous avez essayé de faire ce que vous recommandait Julien, mais ce n'était pas ça. Il indiquait que le test IF n'avait pas de sens. Cela donnerait donc le code suivant :

CREATE FUNCTION suivi_mise_a_jour() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.date:= current_timestamp ;
RETURN NEW ;
END ;
$BODY$ LANGUAGE plpgsql;

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

Bonsoir,

Merci pour vos aides. J'ai acquis de nouvelles connaissances sur les fonctions trigger ainsi que ls triggers.
testés, cela renvoie une entière satisfaction excepté juste une amélioration: Impossible de mettre à jour la table 'member' dans la fonction / trigger stockée

Fonction:
CREATE OR REPLACE FUNCTION suivi_mise_a_jour()
RETURNS trigger AS
$BODY$
BEGIN
NEW.mise_a_jour:= current_date ;
RETURN NEW ;
END ;
$BODY$
LANGUAGE plpgsql

Trigger:

CREATE TRIGGER BI_clients
BEFORE INSERT ON clients
FOR EACH ROW
if NEW.mise_a_jour is not null
EXECUTE PROCEDURE suivi_mise_a_jour();

Quelqu'un saurait ou est le problème ?

Merci d'avance

gleu

Administrateur

Re : Débutant demande aide

Il serait bon d'avoir un jeu de tests complet : SQL de création de la table, SQL de création de la fonction, SQL de création du trigger, SQL d'insertion de données ; ainsi que le vrai message d'erreur complet.

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

J'en prends note pour la suggestion .

J'ai pu régler le problème de la fonction trigger ainsi que les triggers before update et before insert.
Un petit rappel et une petite question pour finir cette expérience.
J'avais créé une colonne sur une table (colonne mise à jour) et créé une fonction trigger et 2 triggers qui à chaque fois qu'il ya un insert ou une mise à jour prend la date du jour et l'insère .

Question : si je pars d'un dump que je restore pas de soucis tout se passe bien  pas de doublons. Si j' insère directement ( a la main ) les lignes sont doublées.

Pourquoi postgres se comporte ainsi ?
Comment faire pour ne pas avoir de doublons .

Merci une fois de plus pour votre aide .

gleu

Administrateur

Re : Débutant demande aide

Toujours pas suffisamment précis. Un jeu de tests nous permettrait de mieux comprendre ce que vous faites et ce que réponds la base de données. (je parle uniquement du cas où vous insérez "directement" et que les lignes sont doublées.

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

Mon test pour être précis était d'insérer une nouvelle colonne maj à une de mes tables afin que la purge de cette maj s'effectue sur la base de date (3O jours par exemple). Je précise que cette table contenait déjà des données. J'ai crée une fonction trigger et 2 triggers pour des before update ou before insert. Puisque cette table contenait des données avant, la colonne date était vide pour les données déjà présentes en base.
J'ai lancé la commande update client set maj = (select detectedlast from departement where OID = _OID and WID = _WID) where maj  is null;
à ma grande surprise, à chaque insert, j'ai un doublon de chaque ligne.
Le fait d'avoir des doublons me dérrange et je souhaite avoir qu'une entrée.
Je ne sais si j'ai été clair cette fois c-sur le jeu de test ou pas .

Merci en tous cas pour l'aide

rjuju

Administrateur

Re : Débutant demande aide

Non, comme Guillaume le disait sans moyen de reproduire le comportement, impossible de vous aider.  Il faut donc la définition de la table, de la fonction trigger et du trigger au minimum.

---

Julien.
https://rjuju.github.io/

Mangone

Membre

Re : Débutant demande aide

Voici ma fonction Trigger :

REATE OR REPLACE FUNCTION suivi_mise_a_jour()
RETURNS trigger AS
$BODY$
BEGIN
NEW.mise_a_jour:= current_date ;
RETURN NEW ;
END ;
$BODY$
LANGUAGE plpgsql;

Voici mes Triggers:

CREATE TRIGGER Before Insert_clients
BEFORE INSERT ON tracks_transmitter
FOR EACH ROW
EXECUTE PROCEDURE suivi_mise_a_jour();

CREATE TRIGGER BeforeUpdate_clients
BEFORE UPDATE ON tracks_transmitter
FOR EACH ROW
EXECUTE PROCEDURE suivi_mise_a_jour();

Ces 2 triggers fonctionnent correctement car elles remplissent la colonne mise_a_jour.

gleu

Administrateur

Re : Débutant demande aide

Un jeu de tests est l'ensemble des requêtes SQL qui nous permettrait de reproduire le problème constaté. On n'a toujours pas ça. Dans votre description de 23h41, vous parlez d'INSERT qui doublerait les lignes au lieu de les mettre à jour. Soit. Mais vous n'indiquez qu'une commande UPDATE. Donc d'où viennent les INSERT ? Bref, encore une fois (et pour la dernière fois), il nous faut un jeu de tests complet sinon on va y passer des heures.

---

Guillaume.

Mangone

Membre

Re : Débutant demande aide

Voici mon jeu de tests:

#!/bin/bash

#SCRIPT de sauvegarde de la table clients
#A exécuter en tant que postgres ou via "sudo -u postgres script.sh"

PGPATH="/usr/bin"
QUIET="-q"

#### TODO : VARIABLE A CHANGER SI BESOIN
CPV_DB_NAME="CORTEX" #Nom de la base de données cible
CPV_DB_PORT=5345 #Numéro de port associé au cluster
REST_D="./" #Dossier ou se trouve le .dump

function sql_query(){
sudo -u postgres psql -p "$CPV_DB_PORT" -d "$CPV_DB_NAME" -c "$1"
}

#1)Ajout de colonne dans la table clients
sql_query "Alter table clients add column mise_a_jour date;"

#2)mise en place des données de la colonne MAJ
sql_query "update clients set mise_a_jour =(select DETECTEDLAST from départements where oid=_oid and wid=_wid) where mise_a_jour is null;"

#3)Sauvegarde de la table clients
sudo ./dump_clients.sh

#4)Re-création de la nouvelle table clients
sql_query "alter table clients drop constraint fk723362laad036112";
sql_query "alter table clients rename to première_table_clients";
sql_query "create table clients (_oid int8 not null, _wid int8 not null, tracks_oid int8 not null, tracks_wid int8 not null, mise_a_jour date, primary key (_oid, _wid, tracks_oid, tracks_wid))";

#5)Remplissage de la table clients
sudo -u postgres pg_restore -p $CPV_DB_PORT -d $CPV_DB_NAME -a -F c -t clients  --disable-triggers --verbose  ./clients.dump

#7)Mise en place du nouvel index
sql_query "create index idx_clients_mise_a_jour on clients(mise_a_jour)" ;

sudo -u postgres psql -p $CPV_DB_PORT -d $CPV_DB_NAME -f ./trigger.sql

Commentaire : lorsque j'execute ce script basé sur la sauvegarde de la table clients, je n'ai aucun doublon.
Lorsque je fais une insertion manuelle sans passer par un dump de cette table, j'ai des doublons de toutes les lignes même au niveau des dates incrémenter dans cette nouvelle colonne .
Mon objectif est de ne pas  dumper cet table, donc de ne pas restorer et par conséquent de ne pas avoir de doublons .

Merci par avance

rjuju

Administrateur

Re : Débutant demande aide

C'est assez compliqué de suivre votre exemple sans savoir clairement les objets créés.  Voici par exemple un test complet, base sur votre différents messages:

CREATE TABLE mangone(id integer, val text, dt timestamptz);
CREATE FUNCTION suivi_mise_a_jour() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.dt:= current_timestamp ;
RETURN NEW ;
END ;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER suivi_mise_a_jour BEFORE INSERT OR UPDATE ON mangone
    FOR EACH ROW EXECUTE PROCEDURE suivi_mise_a_jour();

insert into mangone(id, val) values (1, 'test');
INSERT 0 1

select * from mangone;
 id | val  |              dt
----+------+-------------------------------
  1 | test | 2018-06-10 15:15:25.115889+02
(1 row)

Comme vous pouvez le voir, une insertion manuelle ne provoque pas de doublons.  Vous faites donc quelquechose de mal, mais impossible de savoir quoi.  Fournissez-nous un exemple complet  pour que l'on sache exactement ce que vous faites, autrement on ne pourra pas aller plus loin.

---

Julien.
https://rjuju.github.io/

Mangone

Membre

Re : Débutant demande aide

Merci pour la démonstration, elles est claire et je comprends bien votre démarche.
Le soucis que j'ai, c'est que cette table clients ou mangone comme vous l'avez appelé contenait des données et je ne voulais pas au moment de la purge que celle-ci soit ignorées parce que n'ayant pas de date (il n'existait pas de colonne date avant).
Je pense que sans passer par un pg dump et un pg restore qui garantit les "non doublon", il est presque impossible de les éviter.
Pour une table ne contenant aucune cela fonctionne convenablement mais si elle contenait des données et après avoir fait 
sql_query "update clients set mise_a_jour =(select DETECTEDLAST from département where oid=_oid and wid=_wid) where mise_a_jour is null;" , les doublons sont inévitables.