Problème trigger mise à jour d'une colonne
Bonjour à tous,
Je possède deux tables avec géométrie: communes et site:
Je souhaite récupérer automatique la liste des communes que chaque site intersecte sans prendre en compte les contours qui se touchent.
La mise à jour du nom des communes pour chaque site déjà existant dans la base fonctionne très bien:
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
UPDATE geo_site
SET nom_communes =
(SELECT liste_communes --liaison entre le nom_site de la table et celui du tableau de la requête
FROM
(SELECT nom_site, array_agg(communes)as liste_communes -- concatène les communes par site
FROM
(SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes -- sélection des communes qui intersectent les sites
FROM geo_site, bdcarto_commune
WHERE st_intersects (geo_site.the_geom, bdcarto_commune.geom)
EXCEPT
SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes -- enlève de la sélection les communes qui touchent seulement les contours des sites
FROM geo_site, bdcarto_commune
WHERE st_touches (geo_site.the_geom, bdcarto_commune.geom)
) AS site_intersect_communes
GROUP BY nom_site
) as liste_communes_par_site
WHERE geo_site.nom_site = liste_communes_par_site.nom_site)
; |
Par contre lorsque je souhaite automatiser avec un trigger, pour l'ajout d'un nouveau site, j'ai un petit souci. Il me récupère bien les communes qui intersectent et qui ne font pas que toucher la limite du site, mais il me répète autant de fois la communes qu'il y a de site....
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
|
CREATE OR REPLACE FUNCTION calc_liste_communes()
RETURNS trigger AS
$BODY$
begin
IF NEW.nom_communes is null THEN
NEW.nom_communes = (SELECT liste_communes FROM
(SELECT nom_site, array_agg(communes) as liste_communes FROM
(SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes
FROM geo_site, bdcarto_commune
WHERE st_intersects (geo_site.the_geom, bdcarto_commune.geom)
EXCEPT
SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes
FROM geo_site, bdcarto_commune
WHERE st_touches (geo_site.the_geom, bdcarto_commune.geom)
) AS site_intersect_communes
GROUP BY nom_site
) as liste_communes_par_site
WHERE geo_site.nom_site = liste_communes_par_site.nom_site);
end if;
IF NEW.nom_communes is not null THEN
NEW.nom_communes = replace(replace(replace(replace(NEW.nom_communes,'"',''), ',',', '),'{',''),'}',''); -- pour une meilleur visibilité cf résultat de array_agg
end if;
return NEW;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION calc_liste_communes()
OWNER TO postgres; |
et le trigger sur site:
Code:
1 2 3 4 5 6
|
CREATE TRIGGER add_liste_commune
AFTER INSERT OR UPDATE
ON geo_site
FOR EACH ROW
EXECUTE PROCEDURE calc_liste_communes(); |
Si quelqu'un a une petit idée de mon erreur et du pourquoi il me répète la commune autant de fois qu'il existe de site dans la base alors que l'update fonctionne bien?
cordialement