CREATE OR REPLACE FUNCTION update_rayon_seveso()
  RETURNS trigger AS
$BODY$
DECLARE 
nb integer;
v_nom character varying;
BEGIN


SELECT COUNT(*),foo.nom INTO nb, v_nom FROM dblink('hostaddr=xxxxxx port=xxxxxx dbname=xxxxxx user=postgres password=xxxxxx', 'SELECT wkb_geometry, nom, priorite FROM seveso') 
  AS foo (geom geometry, nom character varying, priorite integer) 
  WHERE ST_contains (foo.geom, NEW.wkb_geometry)
  GROUP BY foo.nom, foo.priorite
  HAVING count(*) > 0
  ORDER BY priorite
  LIMIT 1;

	IF (nb IS NULL) THEN
	    NEW."5_rayon_seveso" = 'Aucun';    
	ELSE 
		NEW."5_rayon_seveso" = v_nom;
	END IF ; 	
	    
  
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_seveso() OWNER TO postgres;

CREATE TRIGGER update_rayon_seveso
  BEFORE INSERT OR UPDATE
  ON etab
  FOR EACH ROW
  EXECUTE PROCEDURE update_rayon_seveso();

-- Function: update_rayon_seveso_test()

-- DROP FUNCTION update_rayon_seveso_test();

CREATE OR REPLACE FUNCTION update_rayon_seveso_test()
  RETURNS trigger AS
$BODY$
DECLARE 
nb integer;
v_nom character varying;
BEGIN


SELECT COUNT(*),foo.nom INTO nb, v_nom FROM dblink('hostaddr=xxxxxx port=xxxx dbname=xxxxx user=postgres password=xxxxx', 'SELECT wkb_geometry, nom FROM seveso') 
  AS foo (geom geometry, nom character varying) 
  WHERE ST_contains (foo.geom, NEW.wkb_geometry)
  GROUP BY foo.nom;

	IF (nb =0) THEN
		NEW."5_rayon_seveso" = 'Aucun';
	ELSE 
		NEW."5_rayon_seveso" = v_nom;
	END IF ; 	
	    
  
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_seveso_test() OWNER TO postgres;



CREATE TRIGGER update_rayon_seveso_test
  BEFORE INSERT OR UPDATE
  ON etab
  FOR EACH ROW
  EXECUTE PROCEDURE update_rayon_seveso_test();