effectuer des opérations (*, / ,..) entre des données d'une meme table

MINIX35 · 22/01/2014 18:21:16

Bonjour,
je commence postgres depuis qq jours et dois effectuer des opérations d'abord sur une même table et ensuite sur des tables différentes d'une même base.
J'utilise des triggers et connais des difficultés, je n'arrête pas d'avoir "ERROR : dupplicate declaration at or near "xxxxx" LINE ..

Voila ce que je "triture" depuis ce matin, passant de forums en forums.

DECLARE

list_matable CURSOR ( matable numeric (10,2) IS SELECT colonne1_de matable, colonne2_dematable
list_matable CURSOR ( matable varchar) IS SELECT colonneresultat_dematable

BEGIN
IF NEW.colonne2_dematable IS NOT NULL
NEW.colonneresultat_dematable = NEW.colonne1_dematable * New.colonne2_de ma table

RETURN NEW;

END;

et quelles sont les différences entre triggers et fonctions dans postgres

Cela doit pourtant être simple mais je comprends pas qqchose, si vous pouvez m'éclairer un peu
merci par avance

gleu · 22/01/2014 23:12:35

Vous définissez deux fois list_matable, d'où le message de double déclaration.

MINIX35 · 27/01/2014 13:49:54

Bonjour, et merci de me répondre, c'est pas si fréquent quand ces sont des questions de débutants.

J'étais absent ce WE, j'ai corrigé le code comme vous me l'indiquez, maintenant j'ai :
DECLARE
-- ma colonne1_de ma table est numéric et ma colonne2_de matable est en character varying
-- ma colonne2_de matable est en character varying
-- ma colonneresultat_de matable est en numeric

list_matable CURSOR ( matable numeric (10,2) IS SELECT colonne1_de matable ( numeric), colonne2_dematable (varchar), colonneresultat_dematable (numeric);

BEGIN
IF (colonne2_dematable IS NOT NULL) THEN -- colonne2 est varchar ?
NEW.colonneresultat_dematable = NEW.colonne1_dematable * New.colonne2_de ma table
END IF;
UPDATE colonneresultat_dematable
RETURN NEW;
END;

J'ai comme réponse " syntax error .. RETURN NEW

MINIX35 · 27/01/2014 13:58:21

j'ai tapé trop vite sur mon clavier..

mais ou est ma boulette, ça parait si simple...
C'est pas le fait d'avoir une donnée en varchar ? qui m'empêcherai d'enregistrer mon resultat dans ma base ?

j'ai appliqué différentes "modéles" via internet, mais sans plus..
merci bien de votre coup de pouce

MitsuTomoe · 27/01/2014 14:40:13

Bonjour,
voici un exemple de fonction appelée dans un trigger :

CREATE FUNCTION stamp_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
nom_base varchar;
    BEGIN
        SELECT INTO nom_base current_database();
        NEW.dat_creat := current_timestamp;
        NEW.user_db_creat := nom_base;
        RETURN NEW;
    END;
$$;

ALTER FUNCTION public.stamp_insert() OWNER TO toto;

et le trigger :

CREATE TRIGGER stamp_insert
    BEFORE INSERT ON matable
    FOR EACH ROW
    EXECUTE PROCEDURE stamp_insert();

Espérant vous aider

Alex

MitsuTomoe · 27/01/2014 15:14:28

En relisant votre message, je crois comprendre la confusion que vous faites.
Un trigger (= déclencheur en français) est un évènement que vous pouvez intercepter pour mettre du code .
Exemple ci-dessus pour renseigner base d'origine et date / heure création à chaque insert.
Si, comme je crois avoir compris, vous ajoutez un champ calculé dans votre table, il vous faut faire 2 choses :
1) Un update pour l'existant, par exemple :

 UPDATE matable set colonneresultat_dematable = colonne1_dematable * colonne2_dematable
               WHERE colonne2_dematable IS NOT NULL;

2) Une fonction appelée dans un trigger "before insert or update" qui fera simplement :

CREATE FUNCTION majtot() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    BEGIN
        IF (colonne2_dematable IS NOT NULL) THEN
NEW.colonneresultat_dematable := NEW.colonne1_dematable * New.colonne2_de ma table;
END IF;
        RETURN NEW;
    END;
$$;

ALTER FUNCTION public.majtot() OWNER TO toto;

Notez le ":=" pour l'affectation et le ";" en fin de ligne .

La création du trigger :

CREATE TRIGGER trg_majtot
    BEFORE INSERT OR UPDATE ON matable
    FOR EACH ROW
    EXECUTE PROCEDURE majtot();

Avec ça, toutes les modifs et créations calculeront le nouveau champ.

Dernière modification par MitsuTomoe (27/01/2014 15:15:05)

MINIX35 · 27/01/2014 16:22:38

Merci bien, c'est sympa de m'accorder qq minutes, vos explications sont (pour moi) simples et constructives , ça m'aide vraiment

Donc, si j'ai compris, je crée ma fonction de calcul, je crée mon trigger qui appellera ma fonction précédente, Ok ? en fait c'est pas si compliqué ..
Je travaille avec pgadmin, je les enregistre tous ensemble (je déclare > puis update > puis fonction > puis appel de la fonction ?) ou il faut que je crée mon trigger et ma fonction indépendamment l'un de l'autre ?

Le "NEW resultat" sera directement envoyé dans son champ ?
Alors cette fonction je peux donc aussi l'appeler dans d'autres triggers ?

MitsuTomoe · 27/01/2014 22:58:15

J'utilise phpPgAdmin et psql et ne connais pas PgAdmin, je ne peux rien dire là dessus.
Pour le reste, lorsqu'une fonction est appelée dans un trigger, certaines variables sont créées et valorisées automatiquement par PostgresSQL :
NEW : contient la ligne qui va être enregistrée (valable en UPDATE ou INSERT uniquement). On accède aux champs avec NEW.champ1, NEW.champ2...etc... Si on modifie un champ et que le trigger se termine bien, cette nouvelle valeur sera stockée.
OLD : la ligne avant les modifs (valable en UPDATE ou DELETE uniquement)
Pour plus d'infos voir Triggers procedures .
La fonction peut être appelée d'autres triggers, on peut même faire une fonction "générique" qui teste le cas INSERT/UPDATE/DELETE et effectue des traitements spécifiques.

Exemple :

IF (TG_OP = 'INSERT') THEN
 NEW.dat_creat := current_timestamp;
 NEW.user_db_creat := current_user;       

ELSEIF (TG_OP = 'UPDATE') THEN
   NEW.dat_tran := current_timestamp;
   NEW.user_db_tran := current_user;
END IF;

Je ne parle que des triggers au niveau ligne, en 9.3 il y a les "event triggers" qui se déclenchent lors des ordres DDL (CREATE TABLE, DROP TABLE...)
mais vous verrez ça plus tard...

MINIX35 · 28/01/2014 09:30:03

Merci beaucoup pour ce guidage, je vais pouvoir travailler sur mon trigger avec du sens, et tenter une fonction générique (pas à pas) et mes triggers ensuite.
J'ai été sur le lien, c'est pour du 9.3 ( je suis en 9.1 et n'ose pas changer de version..), mais les infos de ce tuto sont quasi toutes exploitables pour ma version.
je vous tiens au courant fin de semaine, j'ai du pain sur la planche..
merci encore.

MINIX35 · 28/01/2014 20:56:45

bonsoir, je reviens vers vous prématurément,

j'ai du travailler mon trigger avec " DROP FUNCTION".tout en suivant vos conseils.

Dans postgres (via pgadmin) j'ai différentes parties (fonctions > séquences > tables et fonctions triggers) et dans chaque table je peux encore ajouter des triggers "individuels", très sincèrement je ne vois pas à quoi ça sert mais ça sert sûrement à qqchose ?
Pour faciliter votre lecture, le trigger que je dois mettre en place est fait pour multiplier une surface par un coefficient de chute et ainsi obtenir une surface utile, d’où les noms de mes colonnes (art_vch_surface, art_dec_coefchute.. et art_dec_surfaceutile)

J'ai donc suivi votre procédure sauf que je ne suis pas sûr de la place de mon update

1/ Création de mon trigger dans ma table "article", comme ceci :

-- Trigger: calc_surfaceutile on article
-- DROP TRIGGER calc_surfaceutile ON article;
CREATE TRIGGER calc_surfaceutile
BEFORE INSERT
ON article
FOR EACH ROW
EXECUTE PROCEDURE calc_surfaceutile();

2/ implantation de ma fonction avec l'update ( mes updates?), dans l'espace "triggers" situé après mes toutes mes tables, comme ceci :

BEGIN

-- Mon update de l'existant
UPDATE article
SET NEW.art_dec_surfaceutile = NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface
WHERE art_int_id = art
AND
art_vch_surface IS NOT NULL;

-- ma fonction calcul appelée par mon trigger
IF (art_vch_surface IS NOT NULL) THEN
NEW.art_dec_surfaceutile := (NEW.art_dec_coefchutearticlepopup * art_vch_surface);
END IF;

-- mon update après calcul
UPDATE article
SET NEW.art_dec_surfaceutile = NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface
WHERE art_int_id = art
AND
art_vch_surface IS NOT NULL;

RETURN NEW;
END;

Tout ça est ensuite synthétisé directement par postgres et me donne sur mon panneau SQL :

-- Function: calc_surfaceutile()

-- DROP FUNCTION calc_surfaceutile();

CREATE OR REPLACE FUNCTION calc_surfaceutile()
RETURNS trigger AS
$BODY$BEGIN

UPDATE article
SET NEW.art_dec_surfaceutile = NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface
WHERE art_int_id = art
AND
art_vch_surface IS NOT NULL;
e ma table qui est prévu p
IF (art_vch_surface IS NOT NULL) THEN
NEW.art_dec_surfaceutile := (NEW.art_dec_coefchutearticlepopup * art_vch_surface);
END IF;

UPDATE article
SET NEW.art_dec_surfaceutile = NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface
WHERE art_int_id = art
AND
art_vch_surface IS NOT NULL;

RETURN NEW;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION calc_surfaceutile()
OWNER TO postgres;

Je n'ai plus d'erreurs à l'enregistrement du trigger, et sans plantage, pour moi c'est un peu nouveau....
Seulement le résultat du calcul n'est pas enregistré dans le champ qui existe pour cela dans ma table ?

Je me demande si cela ne viendrait pas du fait que j'ai mis BEFORE INSERT, puisque le calcul doit se faire après avoir entré le coef de chute dans mon IHM ?
Je dois peut être mettre AFTER INSERT + UPDATE dans mon trigger, je vais essayer mais demain.... ? pour cela à priori il faut que j’enlève le précédent, au risque de planter ?

En tout cas je comprend mieux la modélisation d'un trigger, mais j'ai encore de la route à faire pour maîtriser un peu plus postgres

En vous remerciant encore
Daniel

MitsuTomoe · 29/01/2014 00:01:24

Je me suis mal fait comprendre.
Soit une table "matable" avec un champ id de type integer et un champ alp de type varchar(10).
Si vous avez un trigger "before update" de la table "matable" qui appelle la fonction "mon-calcul", l'enchaînement est le suivant :

1) Un client (appli, psql...) exécute

UPDATE matable set alp = "a" WHERE id = 1;

2) PostgreSQL déclenche le trigger qui active la fonction mon-calcul.
Cette fonction prend la main juste avant que l'update soit réalisé.
Admettons que la fonction mon-calcul contienne une ligne :

IF (NEW.alp = "a") THEN
  NEW.alp := "x"
END IF;

3) Cette ligne est suffisante pour que la ligne soit stockée avec les valeurs 1 et "x" .
Il ne faut surtout pas faire d'update de matable dans la fonction, car le trigger sera déclenché qui activera la fonction avec l'update qui...etc...
Pour résumer, dans une fonction trigger before update, vous êtes en train de faire l'update, et vous pouvez intervenir "au vol" pour faire les contrôles que vous voulez. Dans votre fonction trigger, il ne devrait y avoir que :

-- Function: calc_surfaceutile()
-- DROP FUNCTION calc_surfaceutile();
CREATE OR REPLACE FUNCTION calc_surfaceutile()
  RETURNS trigger AS
$BODY$BEGIN    

    BEGIN
    IF (NEW.art_vch_surface IS NOT NULL) THEN
        NEW.art_dec_surfaceutile := (NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface);
    END IF;
    RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calc_surfaceutile()
  OWNER TO postgres;

Le trigger devrait ressembler à ça :

DROP TRIGGER calc_surfaceutile ON article;
  CREATE TRIGGER calc_surfaceutile
  BEFORE INSERT OR UPDATE
  ON article
  FOR EACH ROW
  EXECUTE PROCEDURE calc_surfaceutile();

Lorsque ce sera en place, tous les insert et update mettront à jour la colonne art_dec_surfaceutile de la table article.
Il y a la mise à jour des lignes existantes à faire, soit au préalable (avant de créer le trigger), soit en ayant désactivé le trigger le temps de l'update , soit en faisant une fausse modif pour déclencher le trigger sur toutes les lignes.

MINIX35 · 29/01/2014 18:22:00

bonjour,

voila mon trigger
-- Trigger: trigger-calc_surfaceutile on article
-- DROP TRIGGER "trigger-calc_surfaceutile" ON article;
CREATE TRIGGER "trigger-calc_surfaceutile"
BEFORE INSERT OR UPDATE
ON article
FOR EACH ROW
EXECUTE PROCEDURE fonction_calc_surfaceutile();

puis ma fonction :
DECLARE
list_article CURSOR (article numeric) IS SELECT art_dec_coefchutearticlepopup (numeric), art_dec_surfaceutile (numeric), art_vch_surface(varchar);

BEGIN
IF (art_vch_surface IS NOT NULL) THEN
NEW.art_dec_surfaceutile := (NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface);
END IF;
RETURN NEW;
END;

Ce qui me donne sur mon panneau Sql
-- Function: fonction_calc_surfaceutile()
-- DROP FUNCTION fonction_calc_surfaceutile();

CREATE OR REPLACE FUNCTION fonction_calc_surfaceutile()
RETURNS trigger AS
$BODY$
DECLARE
list_article CURSOR (article numeric) IS SELECT art_dec_coefchutearticlepopup (numeric), art_dec_surfaceutile (numeric), art_vch_surface(varchar);

BEGIN
IF (art_vch_surface IS NOT NULL) THEN
NEW.art_dec_surfaceutile := (NEW.art_dec_coefchutearticlepopup * NEW.art_vch_surface);
END IF;
RETURN NEW;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fonction_calc_surfaceutile()
OWNER TO postgres;

J'ai compris cette fois mes erreurs d'update(s), enfin je pense....
Mon erreur en dissimulait une autre, mes updates "bouclaient" et ma base n'enregistrait pas ma donnée résultat, et donc glassfish ne répondait pas à mon trigger.
Maintenant que tout semble Ok coté trigger, et Glassfish me renvoie sur une exception :

Exception
javax.servlet.ServletException: javax.ejb.EJBException: Transaction aborted; nested exception is: javax.transaction.RollbackException: La transaction a été marquée comme devant être annulée.
Cause racine
javax.ejb.EJBException: Transaction aborted; nested exception is: javax.transaction.RollbackException: La transaction a été marquée comme devant être annulée.

Cela vient probablement du fait qu'a l'origine je devais entrer manuellement le résultat de ma fonction ( surfaceutile) directement par sa saisie dans mon IHM, .. et maintenant il y aurait collision dan mes données.. c'est que je crois comprendre ?

Est il possible de lever cette exception ( que pour cette donnée) en utilsant le code fonction ou mon trigger ?
Cette méthode ( voir lien ci dessous) est elle intégrable dans ma fonction ?

http://books.google.fr/books?id=kVQk9iq … on&f=false

N'ayant pas cette exception si j'enlevais l'update du trigger, j'ai supprimé l'update du trigger en le positionnant sur after/before + insert >> pas d'"exception" mais pas non plus d'enregistrement dans ma base
puis j'ai alors déplacé l'update dans ma fonction, mais ça ne change rien.. update = exception

Je vais voir coté jpa / jsp si je peux faire qqchose ( je travaille avec Netbeans et struts) sous linux

En tout cas, maintenant je crois être capable de faire des triggers, certes simples, mais je vais les complexifier en avançant doucement,
daniel

MitsuTomoe · 29/01/2014 23:54:50

La déclaration de curseur ne sert à rien . Pour Java, je suis totalement incompétent sur le sujet. Essayez dans Forum PostgreSQL.fr et Java .
Si vous voulez faire des triggers plus compliqués, je peux vous donner des tonnes d'exemples, de quelques dizaines à quelques centaines de lignes.
Plutôt par MP, pour ne pas surcharger inutilement le forum.
Alex

MINIX35 · 30/01/2014 12:32:33

Bonjour,

je vais enlever la déclaration de curseur,
Bien sûr, si vous pouviez m'envoyer des exemples de triggers, ils seront les bienvenus, je vais en avoir besoin, et je pourrai m'en inspirer pour comprendre.
je vous envoie mon mail via MP.

Concernant ma difficulté avec l'exception, j'ai commencé à replonger dans mes codes java.
Je ne sais pas si en insérant une colonne dans ma base directement par ma fonction, l'exception serait inactive , je vais essayer au cas ou ..
Les explications de ce lien sont maintenant à ma portée : http://postgresql.developpez.com/docume … atetrigger
Je ne saurai assez vous remercier pour ce sacré coup de main, c'est pas si fréquent d'avoir de l'aide intelligible pour débutant sur des forums
Daniel

MitsuTomoe · 30/01/2014 14:08:09

Le mail dans mon profil était obsolète, je l'ai corrigé.
Bonne continuation

Alex

MINIX35 · 31/12/2014 17:37:11

Bonjour Alex,

J'en "profite" pour vous présenter mes vœux pour 2015, santé, réussite professionnelle et accomplissement personnel bien sûr
Je reviens aussi puisque vous m'aviez déjà sortie d'affaire il y a un an (.. mais mes vœux sont quand même sincères..).
Je dois retourner sur des triggers pour cette fois effectuer des opération entre champs de tables différentes.
J' essaie depuis 2 jours et j'ai une difficulté pour aboutir.

Je travaille sur une appli web qui, à partir d'un formulaire charge des données dans postgres, tout cela est OK avec struts.
Maintenant je dois effectuer des opération entre plusieurs tables à la fois pour récupérer le résultat du calcul dans l'une d'elle et l'afficher dans l'Ihm.

SI j'active mon trigger, je plante glassfish, par contre sans le trigger mes données s'enregistrent en bd mais en "non calculées".
Pour faire simple, j'indique mon trigger qui plante (avec 3 tables seulement).

J'ai donc 3 tables :
table1 ==> champ.result.table
table1 ==> champ.tri.table
table2 ==> champ.ctrl.table

voila ce qui ne marche pas ( fonction test)
Ma fonction
-- Function: test()
-- DROP FUNCTION test();
CREATE OR REPLACE FUNCTION test()
RETURNS trigger AS
$BODY$
DECLARE
table1 numeric;
table2 numeric;

BEGIN
SELECT INTO table2 champ.ctrl.table();
SELECT INTO table1 champ.tri.table(), champ.result.table();
NEW.champ.result.table := table1 champ.tri.table* table2.champ.ctrl.table ;
RETURN NEW;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test()
OWNER TO postgres;

Ensuite la fonction "test" est appelée directement par pgadmin
CREATE TRIGGER "test"
BEFORE INSERT OR UPDATE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE test();

et.. ça plante .. j'ai faux quelque part, mais ou ?

Pouvez vous m'éclairer ? , j'ai tenté de multiples "manip" en croyant comprendre..
Merci bien
Daniel

MINIX35 · 31/12/2014 17:42:06

J'oubliais le message glassfish
Caused by: org.postgresql.util.PSQLException: ERROR: record "new" has no field "champ.ctrl.table"
merci encore

#1 22/01/2014 18:21:16

effectuer des opérations (*, / ,..) entre des données d'une meme table

#2 22/01/2014 23:12:35

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#3 27/01/2014 13:49:54

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#4 27/01/2014 13:58:21

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#5 27/01/2014 14:40:13

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#6 27/01/2014 15:14:28

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#7 27/01/2014 16:22:38

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#8 27/01/2014 22:58:15

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#9 28/01/2014 09:30:03

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#10 28/01/2014 20:56:45

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#11 29/01/2014 00:01:24

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#12 29/01/2014 18:22:00

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#13 29/01/2014 23:54:50

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#14 30/01/2014 12:32:33

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#15 30/01/2014 14:08:09

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#16 31/12/2014 17:37:11

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

#17 31/12/2014 17:42:06

Re : effectuer des opérations (*, / ,..) entre des données d'une meme table

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux