gis
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
update_adr_complete
Parameters
Name
Type
Mode
Definition
DECLARE idvoie integer; adrvoie text; BEGIN IF (TG_TABLE_NAME = 'voie') THEN IF NEW.typologie != OLD.typologie OR NEW.nom != OLD.nom THEN UPDATE adresse.point_adresse SET adresse_complete = CONCAT(numero, ' ', NEW.typologie, ' ', NEW.nom) WHERE id_voie = OLD.id_voie; END IF; RETURN NEW; ELSIF (TG_TABLE_NAME = 'point_adresse') THEN -- Cas des différence IF NEW.numero != OLD.numero OR NEW.suffixe IS DISTINCT FROM OLD.suffixe OR NEW.id_voie IS DISTINCT FROM OLD.id_voie OR ST_DISTANCE(NEW.geom, OLD.geom) > 0.0 OR (NEW.valide IS DISTINCT FROM OLD.valide AND NEW.valide) THEN -- Cas où id_voie est null, calculer un nouvel id_voie IF NEW.id_voie IS DISTINCT FROM OLD.id_voie OR (NEW.id_voie IS NULL AND NEW.valide) OR ST_DISTANCE(NEW.geom, OLD.geom) > 0.0 THEN SELECT adresse.get_id_voie(NEW.geom) into idvoie; -- Aucune voie dévérouillée trouvée IF idvoie IS NULL THEN return NULL; END IF; NEW.id_voie = idvoie; END IF; -- Vérification que la nouvelle adresse n'existe pas déjà IF (NEW.numero != OLD.numero OR NEW.suffixe IS DISTINCT FROM OLD.suffixe OR NEW.id_voie IS DISTINCT FROM OLD.id_voie) AND (SELECT adresse.num_exists(NEW.numero, NEW.suffixe, NEW.id_voie)) THEN return NULL; END IF; -- Modification du nom complet de la voie SELECT nom_complet into adrvoie FROM adresse.voie WHERE id_voie = NEW.id_voie; IF NEW.suffixe IS NOT NULL THEN NEW.adresse_complete = CONCAT(NEW.numero, ' ', NEW.suffixe, ' ', adrvoie); ELSE NEW.adresse_complete = CONCAT(NEW.numero, ' ', adrvoie); END IF; END IF; RETURN NEW; END IF; RETURN NULL; END;