Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 jeu. 31 juillet 2008 12:36

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

je tente de réaliser mon premier déclencheur,

j'ai une table parcelle.
je souhaite que lors de la mise à jour ou de l'insertion d'une nouvelle ligne (surtout de la colonne géométrique)
le déclencheur me permette de calculer automatiquement la surface.
je dois avouer que je suis pas très fort en "langage" alors j'ai du mal.

j'ai testé dans le panneau SQL de PgAdmin III de faire la requête suivante :

CREATE FUNCTION trig_surf_parcelle() RETURNS trigger AS $parcelle$
    BEGIN
        IF(TG_OP = 'UPDATE')THEN
        UPDATE parcelle SET surface_par = area (the_geom) from parcelle;
        END IF;
    END;
    
LANGUAGE 'plpgsql';

CREATE TRIGGER trig_surf_parcelle AFTER INSERT ON parcelle
    FOR EACH ROW
    EXECUTE PROCEDURE trig_surf_parcelle();

il y a l'erreur suivante :

ERREUR:  chaîne entre guillemets dollars non terminée sur ou près de « $parcelle$

LIGNE 1 : ... FUNCTION trig_surf_parcelle() RETURNS trigger AS $parcelle$

État SQL :42601
Caractère : 57

je ne sais même pas si le langage PLpgsql est bien istallé

Pour pouvoir utiliser un langage d'écriture de fonctions dans une base de donnée Postgresql, il faut activer le support du langage pour la base. Cette opération est à répéter pour chaque base qui le nécessite.

La commande est la suivante: createlang plpgsql nomdelabase -Unomdupropriétaire pour plus de détail, se référer au manuel de createlang : man createlang


la commande"createlang plpgsql nomdelabase -Unomdupropriétaire" ne fonctionne pas mais je la tape peut-être au mauvais endroit?

avez-vous des pistes ?

Hors ligne

 

#2 jeu. 31 juillet 2008 13:25

Yves
Moderateur
Lieu: Aix-les-Bains
Date d'inscription: 22 mars 2006
Messages: 9029
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

Pour utiliser postgis il faut activer le langage plpgsql, donc il est bien activé.

Concernant ton erreur, je ne sais pas si je pourrais t'aider mais la partie $parcelle$ me parait incorrect, ceci dit, c'est facile à dire c'est le message d'erreur wink

Que donne ceci :

Code:

CREATE FUNCTION trig_surf_parcelle() RETURNS trigger AS $$
    BEGIN
        IF(TG_OP = 'UPDATE')THEN
        UPDATE parcelle SET surface_par = area (the_geom) from parcelle;
        END IF;
    END;
$$

Y.


Yves Jacolin, bénévole de l'association GeoRezo.net, agit au nom et pour le compte de l'association - Partageons ce qui nous départage !!  - GeoRezo vous aide ? Aidez GeoRezo !

Hors ligne

 

#3 jeu. 31 juillet 2008 14:03

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

rebonjour,

tout d'abord merci Yves  wink

j'ai réalisé la requête et elle à été faite avec succès.

cependant j'ai essayé d'insérer un polygone par une requête SQL :

Insert into parcelle ( id_section, no_par, the_geom)
values ('11238','0999','POLYGON((535382 2512797,535336 2512733,535345 2512721,535354 2512671,535421 2512739,535383 2512797, 535382 2512797))');


qui marche en temps normal.

mais là, il y a un problème avec le déclencheur, car il n'y a visiblement pas de return ???

ERREUR: control reached end of trigger procedure without RETURN
État SQL :2F005
Contexte : PL/pgSQL function "trig_surf_parcelle"


faut il déclarer une variable ?
j'ai essayé ça mais ....

CREATE OR REPLACE FUNCTION trig_surf_parcelle() RETURNS trigger AS $parcelle$
DECLARE
    surface_par numeric;

    BEGIN
        IF(TG_OP = 'UPDATE')THEN
        UPDATE parcelle SET surface_par = area (the_geom) from parcelle;
        END IF;
        RETURN surface_par;
    END;
$parcelle$
   

LANGUAGE 'plpgsql';


CREATE TRIGGER trig_surf_parcelle AFTER INSERT ON parcelle
    FOR EACH ROW
    EXECUTE PROCEDURE trig_surf_parcelle();


l'erreur associée est la suivante :

ERREUR: erreur de syntaxe sur ou près de « $1 »
LIGNE 1 : UPDATE parcelle SET  $1  = area (the_geom) from parcelle
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "trig_surf_parcelle" near line 6


savez-vous pourquoi ??

Dernière modification par nponzo (jeu. 31 juillet 2008 14:14)

Hors ligne

 

#4 jeu. 31 juillet 2008 14:19

cbredel
Membre
Lieu: Agape Lorraine Nord
Date d'inscription: 20 févr. 2006
Messages: 94
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

A priori le problème que ta fonction retourne un champ surface_par. Par ailleurs,  il semblerait que tu essai de mettre à jour l'ensemble de tes champs surface_par et non pas uniquement l'enregistrement modifié.

Pour ma part, j'utilise les enregistrement NEW et OLD

Je ferais donc quelques chose du genre
UPDATE NEW SET NEW.surface_par=area(NEW.the_geom) from NEW

et avec un RETURN NEW;

Je n'ai pas essayer .. à voir.

Cordialement

Hors ligne

 

#5 jeu. 31 juillet 2008 14:50

cbredel
Membre
Lieu: Agape Lorraine Nord
Date d'inscription: 20 févr. 2006
Messages: 94
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

oups...

En fait tu n'as pas besoin de déclarer ta variable surface_par

En prenant la fonction d'update de mon message précédent, tu rempli directement la colonne que tu veux dans ta table.

Hors ligne

 

#6 jeu. 31 juillet 2008 14:53

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour cbredel,

Jai essayé ce que vous avez suggéré, si je l'ai bien écris :

CREATE OR REPLACE FUNCTION trig_surf_parcelle() RETURNS trigger AS $parcelle$

    BEGIN
        IF(TG_OP = 'UPDATE')THEN
        UPDATE NEW SET NEW.surface_par=area(NEW.the_geom) from NEW
        END IF;
        RETURN NEW;
    END;
$parcelle$
   
LANGUAGE 'plpgsql';

CREATE TRIGGER trig_surf_parcelle AFTER INSERT ON parcelle
    FOR EACH ROW
    EXECUTE PROCEDURE trig_surf_parcelle();


l'erreur associée est similaire à celle de tout à l'heure.

ERREUR: erreur de syntaxe sur ou près de « $1 »
LIGNE 1 : UPDATE  $1  SET  $2 =area( $3 ) from  $1  END IF
                            ^
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "trig_surf_parcelle" near line 4


vous savez pourquoi il me met $1 et $2 au lieu de new ?

Hors ligne

 

#7 ven. 01 août 2008 08:51

ranalisolofo.larrys
Membre
Lieu: Ambolonkandrina
Date d'inscription: 19 juil. 2007
Messages: 40
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

Est ce que tu peux essayer avec cette fonction
Pour la fonction :

Code:

CREATE OR REPLACE FUNCTION surf_parcelle()
  RETURNS "trigger" AS
$BODY$
BEGIN
  /* Met à jour les champs depar et depct */
  NEW.surface_par=area(NEW.the_geom);
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
ALTER FUNCTION surf_parcelle() OWNER TO postgres;

Pour le trigger

Code:

CREATE TRIGGER trig_surf_parcelle AFTER UPDATE
   ON parcelle FOR EACH ROW
   EXECUTE PROCEDURE public.surf_parcelle();

Ainsi, t'as plus besoin de faire le test sur TG_OP et que l'update du champ surface se fait automatiquement sans mettre de requette update.

Hors ligne

 

#8 ven. 01 août 2008 10:39

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

depuis ce matin, j'essaye votre requête.

il me dit que la requête est réalisée avec succès mais ne renvois aucun résultat.

donc les surfaces de mes parcelles ne sont pas calculées.

j'ai essayé d'intégrer un polygone depuis GVSIG et la colonne surface_par n'a pas été mise à jour.

j'ai essayé d'enlever les guillemets de 'trigger' et j'ai relancé la requête.

actuellement il tourne depuis environ dix minute et il me dit que la requête est en cours.

je me suis dit qu'il calculait tous les polygones et que ça prendrait du temps. mais la ça fait longtemps.

d'autant que pour calculer la surface avec  " Update parcelle set surface_par = area (the_geom);" ça ne prends pas autant de temps.

on dirait qu'il boucle et ne s'arrête pas.

bref, je vais mettre à jour toutes les surfaces et essayer avec un seul polygone.

si vous avez d'autres suggestions je suis preneur,

c'est bizarre quand même.

à très bientôt 'wink'

Hors ligne

 

#9 ven. 01 août 2008 10:43

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

Précision,

avec update parcelle set surface_par = area (the_geom), ça modifie 425 parcelles en 531 ms -smile-

comment se fait il que mon trigger ne renvois pas de résultat ???

Hors ligne

 

#10 ven. 01 août 2008 10:49

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

j'ai mis toutes les lignes à jour, il y a une surface pour chaque parcelle à présent.

lors de l'insertion d'un nouveau polygone dans gvsig, il ne met toujours pas à jours le champs.

Hors ligne

 

#11 ven. 01 août 2008 15:07

ranalisolofo.larrys
Membre
Lieu: Ambolonkandrina
Date d'inscription: 19 juil. 2007
Messages: 40
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

C'est un after update, donc si tu fais un insert ca ne se declenche pas.
Par contre, est ce que tu peux changer au lieu d'after update, le mettre en before update, comme ca c'est sur qu'il va mettre la valeur de surface avant meme l'execution de l'update.

Hors ligne

 

#12 ven. 05 septembre 2008 14:31

NyPon
Membre
Date d'inscription: 3 nov. 2008
Messages: 111

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

désolé de ne pas avoir donné de nouvelles avant, mais je ne pouvais pas trop me concentrer là dessus.

à Ranalisolofo : j'ai essayé de remplacer after pas before, mais ça ne marchait pas non plus.

j'ai réussi à résoudre le problème (on m'a surtout bien aidé, car je ne pense pas que j'aurais trouvé avec ma faible expérience et ma connaissance des déclencheurs).

j'avais donc une table parcelle :

  id_par serial NOT NULL,
  id_section integer NOT NULL,
  id_par_mere integer,
  no_par character varying(4) NOT NULL,
  surface_par numeric(10,2),
  the_geom geometry,

dont je voulait mettre à jour le champs surface lors d'une modification d'une parcelle dans une interface cliente (gvsig)

le problème est que lorsque l'on mettait à jours sur la même table, le déclencheur faisait une mise à jour, et comme il se déclenche lors d'une mise à jour, il tournait en boucle (si j'ai bien compris). c'est pour ça qu'il lançait une requête interminable.

nous avons donc créé une table surface_parcelle :

Code:

CREATE TABLE surface_parcelle
(
  id_par serial NOT NULL,
  surface_m2 numeric,
  surface_ha numeric,
  CONSTRAINT cle_surf_par PRIMARY KEY (id_par)
)
WITH (OIDS=FALSE);
ALTER TABLE surface_parcelle OWNER TO postgres;

puis nous avons créé la fonction du déclencheur :

Code:

CREATE OR REPLACE FUNCTION update_surf_parcelle2()
  RETURNS "trigger" AS
$BODY$
DECLARE
    -- Declare les variables.
    la_surface numeric;
    id_parcelle integer;
BEGIN

    SELECT INTO id_parcelle id_par FROM surface_parcelle WHERE id_par = 
NEW.id_par;
 
    IF NOT FOUND THEN           
        insert into surface_parcelle (id_par,surface_m2,surface_ha)
            VALUES ( NEW.id_par, area(NEW.the_geom), 
area(NEW.the_geom)/10000 );   
    END IF;
   
    IF TG_OP = 'UPDATE' AND FOUND THEN
        UPDATE surface_parcelle
            SET surface_m2 =area(NEW.the_geom),surface_ha = 
area(NEW.the_geom)/10000
            WHERE id_par = NEW.id_par;
    END IF;   

RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION update_surf_parcelle2() OWNER TO postgres;

et enfin le trigger :

Code:

CREATE TRIGGER trig_surf_parcelle2
  AFTER INSERT OR UPDATE
  ON parcelle
  FOR EACH ROW
  EXECUTE PROCEDURE update_surf_parcelle2();

ensuite, je connecte gvsig avec postgres et j'intègre ma table parcelle. Je commence l'édition et je déplace un sommet, d'une parcelle. je termine l'édition et je rafraichis les données de ma tables dans PG AdminIII, la surface est bien mise à jour dans la table surface_parcelle.

le champs surface_par dans la table parcelle est devenu inutile et peut être supprimé.

voilà, j'espère que ça sera utile.

je réitère mes excuse pour avoir laissé la discussion en attente pendant plus d'un mois.

merci à tous,

cordialement,

Dernière modification par nponzo (ven. 05 septembre 2008 14:35)

Hors ligne

 

#13 mar. 02 août 2011 09:57

Geo-x
Membre
Lieu: Pau
Date d'inscription: 2 nov. 2010
Messages: 213

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

Je pense avoir trouvé le code qui permettrait la mise à jour dès l'insertion d'une nouvelle géométrie :


Code:

CREATE OR REPLACE FUNCTION update_surf_parcelle() 

RETURNS "trigger" AS $surface$
    BEGIN
        NEW.surface_geo=area(NEW.wkb_geometry);
        RETURN NEW;
    END;

$surface$

LANGUAGE 'plpgsql'IMMUTABLE;
ALTER FUNCTION update_surf_parcelle() OWNER TO postgres;

CREATE TRIGGER update_surf_parcelle BEFORE INSERT OR UPDATE
ON parcelle FOR EACH ROW
EXECUTE PROCEDURE update_surf_parcelle();

En fait j'ai simplement modifié le BEFORE UPDATE en BEFORE INSERT OR UPDATE

Du coup, dès l'insertion d'une nouvelle géométrie, la mise à jour se fait automatiquement.

Codement.

Xavier

Hors ligne

 

#14 mar. 10 juillet 2018 11:09

omaillard
Membre
Date d'inscription: 25 nov. 2016
Messages: 4

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

j'ai quasiment la même question concernant la mise en place d'un strigger en fonctions PostgreSQL.
J'aurais voulu mettre à jour une colonne d'une table existante en fonction de la modification d'une autre colonne.
Cette mise à jour consisterait en un calcul de type (colonne A *55)+ (colonne B*10) sur 5 colonnes.
Après avoir consulter bon nombre de forum pour la rédaction de ma fonction j'en suis arriver :

Code:

CREATE OR REPLACE FUNCTION incendie.v_trigger_pei_2()
  RETURNS trigger AS
$BODY$DECLARE
synthese numeric(20,10);
critere_surface numeric(2,1);
critere_access numeric(2,1);
critere_desserte numeric(20,10);
critere_stabilite numeric(20,10);
critere_pouvoir_ca numeric(20,10);
critere_aleas_feu numeric(20,10);
BEGIN
IF (TG_OP = 'UPDATE') THEN
UPDATE incendie.pei SET pei.synthese = sum ((55*pei.critere_surface) + (10*pei.critere_access) + (10*pei.critere_desserte) + (5*pei.critere_stabilite) + (13*pei.critere_pouvoir_ca) + (7*pei.critere_aleas_feu));END IF; END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION incendie.v_trigger_pei_2()
  OWNER TO postgres;

et

Code:

CREATE TRIGGER update_pei
  AFTER UPDATE OF critere_surface
  ON incendie.pei
  FOR EACH ROW
  EXECUTE PROCEDURE incendie.v_trigger_pei_2();

et vous vous en doutez j'ai un message d'erreur :
ERROR : aggregate functions are not allowed in UPDATE
LINE 1 : UPDATDE incendie.pei SET pei.synthese = sum ((55*pei.critere_...
QUERY : UPDATE incendie.pei^SET pei....
CONTEXT : PL/pgSQL function incendie.v_trigger_pei_2() line 11 at SQL statement

Si vous pouviez m'aider à comprendre  car je bloque depuis des jours.

Dernière modification par omaillard (mar. 10 juillet 2018 11:11)

Hors ligne

 

#15 mar. 10 juillet 2018 11:48

tevrard
Membre
Date d'inscription: 23 mai 2016
Messages: 175

Re: Déclencheurs/triggers - fonctions PostgreSQL

ERROR : aggregate functions are not allowed in UPDATE


=> tu as essayé de rentrer ta somme dans un SELECT ?

SET pei.synthese = (SELECT sum(...) as pei.synthese FROM...)

Hors ligne

 

#16 mar. 10 juillet 2018 11:50

omaillard
Membre
Date d'inscription: 25 nov. 2016
Messages: 4

Re: Déclencheurs/triggers - fonctions PostgreSQL

oui mais je viens quand même de vérifier, le message est identique en tout point

Hors ligne

 

#17 mar. 10 juillet 2018 12:03

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1107

Re: Déclencheurs/triggers - fonctions PostgreSQL

Bonjour,

Passez par une CTE:

Code:

WITH tmp as (
... calcul de somme
) update matable tab set synthese = t.sum 
from tmp t 
where tab.id = t.id

Attention dans votre update:
UPDATE incendie.pei SET pei.synthese: pei n'est pas l'alias de la table incendie.pei.
plutot faire :

Code:

UPDATE incendie.pei p set synthese = t.sum
from tmp t 
where p.id = t.id

Nicolas

Hors ligne

 

#18 mar. 10 juillet 2018 15:02

omaillard
Membre
Date d'inscription: 25 nov. 2016
Messages: 4

Re: Déclencheurs/triggers - fonctions PostgreSQL

bonjour nicolas,

merci pour cette idée qui me parait très bonne néanmoins n'étant pas un très grand développeur. J'avoue avec un peu de honte que je ne comprends pas ce que je dois faire.

dans le cas de with, tmp equivaut à la création d'une table temporaire? le t.sum et le "tmp t"

et dans le second je comprends pas le "p" entre incendie.pei et set synthese

pouvez vous me donner plus d'informations

merci beaucoup,

Hors ligne

 

#19 mar. 10 juillet 2018 15:16

Pascal Boulerie
Membre
Lieu: France
Date d'inscription: 12 sept. 2005
Messages: 1805
Site web

Re: Déclencheurs/triggers - fonctions PostgreSQL

La lettre est un petit surnom donné à la table pour épargner du temps de frappe clavier à celui qui fait la saisie de la requête mySQL.

C'est expliqué dans un cours SQL.


https://mysql.developpez.com/
La page Jointures parle de ce surnom :
https://sqlpro.developpez.com/cours/sqlaz/jointures/


« L'État est désormais quasi déliquescent. » (José Cohen-Aknine, ingénieur X-Ponts, IGPEF, dans Déliquescence et renaissance de l'État.)

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |