Forums PostgreSQL.fr

charlene C

Membre

Trigger SUM/ GROUP BY postgresql

Je souhaiterais que dans ma base se mette à jour automatique ma colonne surf total par site.

J'ai donc mis en place un trigger fonction :

BEGIN
NEW.surface_total:=SUM(ST_Area(new.geom))group by site;
RETURN NEW;
END;

Puis appliquer mon trigger sur ma couche.

Hors lorsque j’essaie d'ajouter une entité dans ma table sous QGIS et d'enregistrer pour voir le résultat voilà le message d'erreur que j'ai :

Erreur PostGIS lors de l'ajout d'entité : ERREUR:  la colonne « site » n'existe pas
LINE 1: SELECT SUM(ST_Area(new.geom))group by site

Je ne comprend pas car ma colonne site existe bien.

Si vous avez des pistes, je suis preneuse....

gleu

Administrateur

Re : Trigger SUM/ GROUP BY postgresql

Généralement, les problèmes de nom proviennent de l'ajout de majuscules dans le nom problématique, auquel cas, il faut placer le nom entre colonne.

Ceci étant, la requête n'a pas de sens pour moi. Quand un fait un GROUP BY sur un SUM, on s'attend à récupérer plusieurs lignes. Or, vous affectez le résultat du SUMM à une colonne. Comment une colonne pourrait enresgitrer plusieurs valeurs pour la même ligne (celle en cours de mise à jour) ?

---

Guillaume.

charlene C

Membre

Re : Trigger SUM/ GROUP BY postgresql

Alors je me suis peut être mal exprimé ou j'ai peut être mal construit ma requête mais voilà ce que je veux et ce que j'ai :

J'ai 1 table surfenh dans lequel j'ai des colonnes dont site, surface, surface_par_site...
Je veux que ma colonne surface_par_site me renvoi une surface en fonction du site. Pour un site, j'aurai donc 1 surface_par_site qui se répétera (oui c'est un peu bête peut être). J'ai donc 100 entités qui ont des surfaces différentes, sur 20 sites. J'aurai donc au final sur ma colonne surface_par_site 100 entités mais seulement 20 sommes différentes.

Pour information j'ai déjà testé :
BEGIN
NEW.surface_total:=SUM(ST_Area(new.geom))group by "site";
RETURN NEW;
END;

ou

BEGIN
NEW.surface_total:=SUM(ST_Area(new.geom))group by 'site';
RETURN NEW;
END;

Il me renvoi toujours la même erreur.

gleu

Administrateur

Re : Trigger SUM/ GROUP BY postgresql

L'erreur de la colonne est due au fait que vous ne l'avez pas préfixé du nom de la table (NEW) alors que vous l'avez bien fait pour surface_total et geom.

Il me semble toujours qu'il faudrait changer la requête (et notamment abandonner le GROUP BY site) mais je ne sais pas exactement par quoi actuellement.

---

Guillaume.

charlene C

Membre

Re : Trigger SUM/ GROUP BY postgresql

Je n'ai effectivement plus d'erreur en préfixant new.site mais il ne me fait quand même pas la surface par site.
Il me renvoi la surface de la nouvelle entité.

De plus j'ai cette fois un nouveau message d'erreur lors de la suppression de l'entité et je suis obligé de rendre inactif le trigger pour supprimer mon entité.
Erreur du fournisseur de données :
      Erreur PostGIS lors de la suppression d'entité : ERREUR:  l'enregistrement « new » n'est pas encore affectée
    DETAIL:  La structure de ligne d'un enregistrement pas encore affecté est indéterminée.
    CONTEXT:  instruction SQL « SELECT SUM(ST_Area(new.geom))group by new.site »
    fonction PL/pgsql pole_technique.calcul_surf_total_site(), ligne 2 à affectation

gleu

Administrateur

Re : Trigger SUM/ GROUP BY postgresql

Ce qui est logique. Avec un "NEW.colonne :=", vous essayez d'affecter une valeur à une colonne d'une ligne supprimée. Je pense que le mieux serait de fournir un exemple complet avec quelques lignes de la table pour qu'on comprenne mieux ce que vous voulez faire.

---

Guillaume.

charlene C

Membre

Re : Trigger SUM/ GROUP BY postgresql

gid    secteur    site    type_surf    surface    code_ent    convention    surface_to
201    ZAC    BEAU SOLEIL NORD    02    233.164802191769            7316
202    ZAC    BEAU SOLEIL NORD    01    150.222470300527            7316
203    ZAC    BEAU SOLEIL NORD    01    181.560618462796            7316
204    ZAC    BEAU SOLEIL NORD    01    100.418935165886            7316
205    ZAC    BEAU SOLEIL NORD    01    111.748459693281            7316
206    ZAC    BEAU SOLEIL NORD    01    56.2034740272345            7316
207    ZAC    BEAU SOLEIL NORD    01    15.8995911087728            7316
208    ZAC    BEAU SOLEIL NORD    01    3533.98304339476            7316
300    ZAC    BEAU SOLEIL NORD    02    2902.80650284431            7316
200    ZAC    BEAU SOLEIL NORD    01    17.3674759944427            7316
348    ZAC    BEAU SOLEIL SUD    02    13440.8542233807            59846
349    ZAC    BEAU SOLEIL SUD    02    5631.21243781833            59846
350    ZAC    BEAU SOLEIL SUD    02    17990.8078116016            59846
351    ZAC    BEAU SOLEIL SUD    02    20049.4232475934            59846
352    ZAC    BEAU SOLEIL SUD    02    2626.74934858792            59846

Je souhaite que si un utilisateur créé une entité sur le site "BEAU SOLEIL NORD" ou supprime un entité, que comme mon champs surface (mis à jour automatiquement), mon calcul de surface_to (qui correspond à la sum des surfaces sur un site) se mette à jour automatiquement afin que tous ce qui en découle (statistique...) se mette à jour directement sans intervention d'un utilisateur.

gleu

Administrateur

Re : Trigger SUM/ GROUP BY postgresql

Ce n'est pas si simple parce que vous allez avoir rapidement un problème de récursion. Si vous aveez un trigger sur UPDATE qui fait lui même un UPDATE de la même table, ça va de nouveau déclencher le trigger, qui va de nouveau faire un UPDATE qui va lui-même déclencher encore le trigger. Et ça continuera indéfinimment. Plus exactement, ça continuera jusqu'à avoir rempli la pile d'éxécution. Le problème vient surtout du fait que vos données ne sont pas correctement normalisées. Vous devriez avoir une table pour le site (avec nom du site et surface totale) et une table pour les éléments (avec la surface de chaque élément).

Sans changer la strtucture de la base, il va falloir vérifier le retour de la fonction pg_trigger_depth() pour s'assurer qu'il s'agit du premier appel du trigger, et faire un UPDATE du style "UPDATE table SET surface_to = (SELECT SUM(...) FROM table WHERE site=NEW.site) WHERE site=NEW.site" (syntaxe à revoir mais l'idée est là.

---

Guillaume.

jmarsac

Membre

Re : Trigger SUM/ GROUP BY postgresql

S'il s'agit de simple consultation avec QGIS, il me semble plus simple de créer une vue :

CREATE VIEW surfaces AS
  WITH cte AS (SELECT SUM(surfaces) AS total, site FROM table GROUP BY site)
  SELECT gid,secteur,site,type_surf,surface,code_ent,convention,cte.total AS surface_to FROM table t LEFT JOIN cte ON t.site = cte.site

et d'utiliser cette vue au lieu de la table dans QGIS; ce qui permet de "laisser tomber" la colonne "surface_to" de la table et le trigger.