Le portail francophone de la géomatique

tevrard · Mon 04 June 2018 11:09

Bonjour,

Je cherche une syntaxe efficace pour écrire une boucle dans un trigger de type "before_insert".

Concrètement, les données que je reçois comportent un attribut code_insee. Je crains cependant que le code insee ne sois pas à jour (avec les fusions de communes etc...)

J'ai donc créé une table de correspondance de type (code_commune, libelle_commune, code_commune_old, lib_commune_old, chef_lieu, annee) avec en clé primaire le triplet (code_commune, code_commune_old, annee)

J'ai mis en place la condition suivante :

Code:

IF NEW.code_insee = bdd_territoires.ref_communes_nouvelles.code_commune_old 
    AND bdd_territoires.ref_communes_nouvelles.chef_lieu = 'N'
    AND annee = (SELECT max(annee) FROM bdd_territoires.ref_communes_nouvelles WHERE code_commune_old ='NEW.code_insee')
THEN  NEW.code_insee = bdd_territoires.ref_communes_nouvelles.code_commune;
      NEW.commune = bdd_territoires.ref_communes_nouvelles.lib_commune;
      NEW.code = bdd_territoires.ref_communes_nouvelles.code_commune;
END IF;

Cependant, il est possible qu'une commune subisse plusieurs modifications :

Par exemple en 2015, les communes 77170 Episy, 77299 Montarlot et 77316 Orvanne ont fusionné pour devenir la commune 77316 Moret Loing et Orvanne.
En 2016 la commune 77316 Moret Loing et Orvanne est devenue 77316 Moret-Loing-et-Orvanne

J'ai donc besoin d'une boucle pour retracer l'historique des modifications.

Je ne sais pas trop quels mots clés utiliser, ni sur quels éléments m'appuyer ma première idée tant que code_commune = code_commune_old part en boucle infinie sur l'exemple précédent.

NB : RTFM, certes. J'ai parcouru la doc rapidement, mais sans avoir vraiment trouvé (compris) ce que je cherchais. Je n'ai malheureusement pas le temps de faire des recherches et des tests approfondis. Je m'en remets donc a vous

Dernière modification par tevrard (Mon 04 June 2018 11:23)

tumasgiu · Mon 04 June 2018 12:33

Salut,

j'opterais pour une mise à jour après insertion,
en un seul update.

Cela présuppose d'avoir une table de correspondance
qui associe pour chaque code insee sa dernière modification.

On peut réaliser çà avec une requête récursive :

Code:

WITH RECURSIVE t(insee_1, insee_2, d) AS(
SELECT code_commune_old, code_commune, 1
UNION
SELECT t.insee_1, r.code_commune ,  d+1 FROM ref_commune_nouvelle r
JOIN t ON t.insee_2 = r.code_commune_old
),
tc AS(
SELECT DISTINCT ON (insee_1) insee_1 code_commune_a_remplacer, insee_2 dernier_code_commune
FROM t
ORDER BY insee_1, d DESC)

UPDATE ma_table
SET code_commune = (SELECT dernier_code_commune WHERE code_commune_a_remplacer = code_commune FROM tc)
WHERE code_commune IN (SELECT code_commune_a_remplacer FROM tc)

Tout cela à intégrer dans un trigger AFTER UPDATE/INSERT.

Dernière modification par tumasgiu (Mon 04 June 2018 15:02)

tevrard · Mon 15 April 2019 18:23

Bonjour,

Désolé pour le temps de réponse ^^ (J'ai dû laisser de côté cette problématique l'année dernière, et c'est de retour au programme aujourd'hui !)

Tout d'abord, Merci à tumasgiu pour sa réponse (et le temps qu'il passe à nous expliquer plein de chose sur le forum !)

J'ai quelques questions à propos de la requête

Code:

SELECT code_commune_old, code_commune, 1
UNION

Il n'y a pas de FROM associé au select?

tevrard · Tue 16 April 2019 08:51

Bonjour,
La doc dit la chose suivante :

La forme générale d'une requête WITH est toujours un terme non récursif, puis UNION (ou UNION ALL), puis un terme récursif. Seul le terme récursif peut contenir une référence à la sortie propre de la requête.

Donc ma question ne tient plus

tumasgiu · Tue 16 April 2019 15:05

Salut,

non votre remarque était juste,
il manque bien une clause FROM dans la partie non récursive de la CTE t.

tevrard · Tue 16 April 2019 16:43

Pour suivi :

Code:

WITH RECURSIVE t(insee_1, insee_2, nb_boucles) AS(
SELECT code_commune, code_commune, 1 FROM test.population_historique
UNION
SELECT t.insee_1, r.com_ap ,  nb_boucles+1 FROM test.mvt_communes_simplifie r
JOIN t ON t.insee_2 = r.com_av
),
tc AS (SELECT insee_1, max(nb_boucles) as nb_boucles FROM t 
    GROUP BY insee_1
    ORDER BY insee_1)

SELECT t.insee_2, sum(poph.population_municipale) as population_municipale FROM t
JOIN tc ON (t.insee_1, t.nb_boucles) = (tc.insee_1, tc.nb_boucles)
JOIN test.population_historique as poph ON t.insee_1 = poph.code_commune
GROUP BY t.insee_2
ORDER BY t.insee_2

On a donc notre table t qui est notre table récursive, celle qui nous sert à retracer l'historique des évolutions de la commune.
Rq : je n'ai qu'un seul code_commune dans test.population_historique, du coup je l'insère dans insee_1 et insee_2 pour "initialiser" la table.

La table tc permet de conserver uniquement les lignes de t qui nous intéressent : c'est à dire les lignes où sont associés le code originel et le dernier code trouvé (nb_boucles est donc maximum)

Le SELECT de sortie permet d'une part de lister les codes communes "restant" (tjrs en vigueur). Ici il permet aussi de sommer les populations des communes ayant fusionnées.

tumasgiu · Wed 17 April 2019 10:15

Merci du suivi !

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

#1 Mon 04 June 2018 11:09

Boucle dans une fonction trigger

Code:

#2 Mon 04 June 2018 12:33

Re: Boucle dans une fonction trigger

Code:

#3 Mon 15 April 2019 18:23

Re: Boucle dans une fonction trigger

Code:

#4 Tue 16 April 2019 08:51

Re: Boucle dans une fonction trigger

#5 Tue 16 April 2019 15:05

Re: Boucle dans une fonction trigger

#6 Tue 16 April 2019 16:43

Re: Boucle dans une fonction trigger

Code:

#7 Wed 17 April 2019 10:15

Re: Boucle dans une fonction trigger

Pied de page des forums