50 lines
1.1 KiB
Python
50 lines
1.1 KiB
Python
|
|
from pycen import con
|
|
# import pycen
|
|
|
|
s1 = '''create trigger get_communnes_intersects after
|
|
insert
|
|
or
|
|
delete
|
|
or
|
|
update
|
|
on
|
|
sites.r_sites_geom for each row execute function sites.refresh_site_comm()'''
|
|
|
|
s2 = '''CREATE OR REPLACE FUNCTION sites.refresh_site_comm()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
BEGIN
|
|
with t1 as (
|
|
SELECT
|
|
id,
|
|
nom_com,
|
|
insee_com,
|
|
geom
|
|
FROM flux_geo.vm_communes_isere
|
|
),
|
|
t2 as (SELECT
|
|
s.id_site,
|
|
json_object_agg(t1.insee_com,t1.nom_com) all_comm,
|
|
max(s.date) date,
|
|
s.geom
|
|
-- FROM s1 s, t1
|
|
FROM sites.r_sites_geom s, t1
|
|
WHERE st_intersects(s.geom,t1.geom)
|
|
and s.id_site = NEW.id_site
|
|
GROUP BY s.id_site,s.geom,s.date
|
|
ORDER BY s.id_site, s.date DESC)
|
|
UPDATE sites.sites SET nom_com_json = t2.all_comm
|
|
FROM t2
|
|
WHERE id = t2.id_site;
|
|
IF NOT FOUND THEN
|
|
UPDATE sites.sites SET nom_com = null WHERE id = t2.id_site;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;'''
|
|
with con.begin() as cnx:
|
|
cnx.execute(s1)
|
|
cnx.execute(s2) |