[opencourrier-Commits] r951 - branches/5.0.0/data/pgsql

[fray...@adullact.net]

Author: fraynaud
Date: 2017-08-11 12:53:24 +0200 (Fri, 11 Aug 2017)
New Revision: 951

Added:
branches/5.0.0/data/pgsql/init_metier_trigger.sql
Log:


Added: branches/5.0.0/data/pgsql/init_metier_trigger.sql
===================================================================
--- branches/5.0.0/data/pgsql/init_metier_trigger.sql (rev 0)
+++ branches/5.0.0/data/pgsql/init_metier_trigger.sql 2017-08-11 10:53:24 UTC (rev 951)
@@ -0,0 +1,51 @@
+-- trigger de construction geom
+/*
+CREATE OR REPLACE FUNCTION pr_maj_tache()
+ RETURNS trigger AS
+$BODY$
+DECLARE
+BEGIN
+ IF (TG_OP='INSERT') THEN
+ UPDATE tache
+ SET geom=ST_Transform(ST_SetSRID(cast(ST_MakePoint(cast(substr(coordinates,1,strpos( coordinates, ',')-1) as float),
+ cast(substr(coordinates,strpos( coordinates, ',')+1) as float)) as geometry),4326),2154)
+ WHERE tache=NEW.tache;
+ RETURN NEW;
+ ELSEIF(NEW.coordinates!=OLD.coordinates or OLD.coordinates is null) THEN
+ UPDATE tache
+ SET geom=ST_Transform(ST_SetSRID(cast(ST_MakePoint(cast(substr(coordinates,1,strpos( coordinates, ',')-1) as float),
+ cast(substr(coordinates,strpos( coordinates, ',')+1) as float)) as geometry),4326),2154)
+ WHERE tache=NEW.tache;
+ RETURN NEW;
+ END IF;
+ RETURN NEW;
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ SET search_path FROM CURRENT;
+*/
+
+
+CREATE OR REPLACE FUNCTION pr_maj_tache()
+ RETURNS trigger AS
+$BODY$
+DECLARE
+BEGIN
+ IF (TG_OP='INSERT' and NEW.coordinates is not null) THEN
+ UPDATE tache
+ SET geom=ST_Transform(ST_SetSRID(cast(ST_MakePoint(cast(substr(coordinates,1,strpos( coordinates, ',')-1) as float),
+ cast(substr(coordinates,strpos( coordinates, ',')+1) as float)) as geometry),4326),2154)
+ WHERE tache=NEW.tache;
+ RETURN NEW;
+ END IF;
+ RETURN NEW;
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ SET search_path FROM CURRENT;
+
+CREATE TRIGGER tr_maj_tache
+ AFTER INSERT OR UPDATE
+ ON tache
+ FOR EACH ROW
+ EXECUTE PROCEDURE pr_maj_tache();