forked from NicolasDEVOUCOUX/rva
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sign_20_vues_gestion.sql
168 lines (116 loc) · 6.94 KB
/
sign_20_vues_gestion.sql
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
/*SIGNALEMENT VOIE ET ADRESSE V1.0*/
/*Creation des vues et triggers nécessaires à la gestion via l'application web-métier */
/*sign_20_VUES_GESTION.sql */
/*PostGIS*/
/* GeoCompiegnois - http://geo.compiegnois.fr/ */
/* Auteur : Grégory Bodet*/
-- ####################################################################################################################################################
-- ### ###
-- ### DROP ###
-- ### ###
-- ####################################################################################################################################################
DROP VIEW IF EXISTS m_signalement.geo_v_rva_signal;
-- ####################################################################################################################################################
-- ### ###
-- ### VUES ###
-- ### ###
-- ####################################################################################################################################################
-- View: m_signalement.geo_v_rva_signal
-- DROP VIEW m_signalement.geo_v_rva_signal;
CREATE OR REPLACE VIEW m_signalement.geo_v_rva_signal AS
SELECT s.id_signal,
s.insee,
s.commune,
s.type_rva,
s.nat_signal,
s.acte_admin,
string_agg(((m.n_fichier || chr(10)) || 'http://geo.compiegnois.fr/documents/metiers/rva/signalement/'::text) || m.media, chr(10)) AS document,
s.observ,
s.op_sai,
s.mail,
s.traite_sig,
s.x_l93,
s.y_l93,
s.date_sai,
s.date_maj,
s.geom
FROM geo_rva_signal s
LEFT JOIN an_rva_signal_media m ON s.id_signal = m.id
GROUP BY s.id_signal, s.insee, s.commune, s.type_rva, s.nat_signal, s.acte_admin, s.observ, s.op_sai, s.mail, s.traite_sig, s.x_l93, s.y_l93, s.date_sai, s.date_maj, s.geom;
ALTER TABLE m_signalement.geo_v_rva_signal
COMMENT ON VIEW m_signalement.geo_v_rva_signal
IS 'Vue géographique éditable pour l''intégration des signalements dans la base de voies et adresses';
-- ####################################################################################################################################################
-- ### ###
-- ### TRIGGER ###
-- ### ###
-- ####################################################################################################################################################
-- #################################################################### FONCTION TRIGGER - GEO_RVA_SIGNALEMENT ###################################################
-- Function: m_signalement.ft_geo_rva_signal()
-- DROP FUNCTION m_signalement.ft_geo_rva_signal();
CREATE OR REPLACE FUNCTION m_signalement.ft_m_geo_rva_signal()
RETURNS trigger AS
$BODY$
DECLARE v_id_signal integer;
BEGIN
-- INSERT
IF (TG_OP = 'INSERT') THEN
NEW.insee :=(SELECT geo_osm_commune.insee AS insee FROM r_osm.geo_osm_commune WHERE st_intersects(new.geom,r_osm.geo_osm_commune.geom) = true);
NEW.commune :=(SELECT geo_osm_commune.commune AS commune FROM r_osm.geo_osm_commune WHERE st_intersects(new.geom,r_osm.geo_osm_commune.geom) = true);
NEW.traite_sig ='2';
NEW.x_l93=ST_X(new.geom);
NEW.y_l93=ST_Y(new.geom);
NEW.date_sai=now();
NEW.date_maj = NULL;
RETURN NEW;
-- UPDATE
ELSIF (TG_OP = 'UPDATE') THEN
NEW.insee=(SELECT geo_osm_commune.insee AS insee FROM r_osm.geo_osm_commune WHERE st_intersects(new.geom,r_osm.geo_osm_commune.geom) = true);
NEW.commune=(SELECT geo_osm_commune.commune AS commune FROM r_osm.geo_osm_commune WHERE st_intersects(new.geom,r_osm.geo_osm_commune.geom) = true);
NEW.x_l93=ST_X(new.geom);
NEW.y_l93=ST_Y(new.geom);
NEW.date_sai=OLD.date_sai;
NEW.date_maj=now();
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION m_signalement.ft_m_geo_rva_signal()
OWNER TO sig_create;
COMMENT ON FUNCTION m_signalement.ft_geo_rva_signal() IS 'Fonction trigger pour mise à jour de la donnée de signalement du référentiel voie/adresse';
-- Trigger: m_signalement.t_geo_rva_signal on m_signalement.geo_rva_signal
-- DROP TRIGGER m_signalement.t_geo_rva_signal ON m_signalement.geo_rva_signal;
CREATE TRIGGER t_geo_rva_signal
BEFORE INSERT OR UPDATE
ON m_signalement.geo_rva_signal
FOR EACH ROW
EXECUTE PROCEDURE m_signalement.ft_geo_rva_signal();
-- FUNCTION: m_signalement.ft_m_geo_v_rva_signal_traite()
-- DROP FUNCTION m_signalement.ft_m_geo_v_rva_signal_traite();
CREATE OR REPLACE FUNCTION m_signalement.ft_m_geo_v_rva_signal_traite()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE') THEN
UPDATE m_signalement.geo_rva_signal SET traite_sig = new.traite_sig WHERE id_signal = new.id_signal;
END IF;
IF (TG_OP = 'DELETE') THEN
DELETE FROM m_signalement.geo_rva_signal WHERE id_signal = old.id_signal;
END IF;
return new;
END
$BODY$;
ALTER FUNCTION m_signalement.ft_m_geo_v_rva_signal_traite()
OWNER TO sig_create;
-- Trigger: t_t1_geo_v_rva_signal_traite
DROP TRIGGER t_t1_geo_v_rva_signal_traite ON m_signalement.geo_v_rva_signal;
CREATE TRIGGER t_t1_geo_v_rva_signal_traite
INSTEAD OF UPDATE OR DELETE
ON m_signalement.geo_v_rva_signal
FOR EACH ROW
EXECUTE PROCEDURE m_signalement.ft_m_geo_v_rva_signal_traite();