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
| CREATE TABLE public.a
(gid SERIAL NOT NULL PRIMARY KEY,
num integer not null,
code_insee VARCHAR(5),
nom varchar(150),
date_saisie date,
annee_saisie integer,
full_name VARCHAR(60),
indexation_1 VARCHAR(150),
indexation_2 VARCHAR(150),
geom geometry(point,2154))
CREATE OR REPLACE FUNCTION "public"."concat_2var" (s1 varchar, s2 varchar, s3 varchar) RETURNS varchar AS
$body$
BEGIN
RETURN s1||s2||s3;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
CREATE OR REPLACE FUNCTION public.maj_a()
RETURNS trigger AS
$BODY$
BEGIN
SELECT code_insee FROM public.com WHERE st_intersects((NEW.geom), geom) INTO NEW.code_insee;
SELECT nom FROM public.com WHERE st_intersects((NEW.geom), geom) INTO NEW.nom;
NEW.nom = UPPER(NEW.nom);
NEW.annee_saisie:= EXTRACT(YEAR FROM CURRENT_DATE);
SELECT DISTINCT public.concat_2var(NEW.code_insee::varchar,'-',NEW.num::varchar) from public.a INTO NEW.indexation_1;
SELECT DISTINCT public.concat_2var(NEW.annee_saisie::varchar,'-',NEW.indexation_1::varchar) from public.a INTO NEW.indexation_2;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER maj_a
BEFORE INSERT
ON public.a
FOR EACH ROW
EXECUTE PROCEDURE maj_a(); |
Partager