Forums PostgreSQL.fr

CREATE TABLE travail.habitat
(
  id_hab serial NOT NULL,
  surf_ha numeric,
  surf_prcent_perim real,
  date_saisie_min date,
  date_saisie_max date,
  descriptif character varying(150),
  id_referentiel integer,
  id_uv integer,
  id_nat_obs integer,
  id_et_cons integer,
  id_restaur integer,
  id_typi integer,
  num_site character varying(9),
  id_dynam integer,
  the_geom geometry,
  CONSTRAINT pk_hab PRIMARY KEY (id_hab),
  CONSTRAINT fk_dynam FOREIGN KEY (id_dynam)
      REFERENCES dynamique (id_dynam) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_et_cons FOREIGN KEY (id_et_cons)
      REFERENCES etat_cons (id_et_cons) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_nat_obs FOREIGN KEY (id_nat_obs)
      REFERENCES nature_obs (id_nat_obs) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_num_site FOREIGN KEY (num_site)
      REFERENCES perim_etude (num_site) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_referentiel FOREIGN KEY (id_referentiel)
      REFERENCES referentiel (id_referentiel) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_restaur FOREIGN KEY (id_restaur)
      REFERENCES restaur (id_restaur) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_typi FOREIGN KEY (id_typi)
      REFERENCES typicite (id_typi) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_uv FOREIGN KEY (id_uv)
      REFERENCES unite_veg (id_uv) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2154)
)
WITH (OIDS=TRUE);
ALTER TABLE travail.habitat OWNER TO postgres;

-- Trigger: trig_maj_id_hab on travail.habitat

-- DROP TRIGGER trig_maj_id_hab ON travail.habitat;

CREATE TRIGGER trig_maj_id_hab
  AFTER INSERT OR UPDATE
  ON travail.habitat
  FOR EACH ROW
  EXECUTE PROCEDURE travail.trig_maj_hab();

CREATE TABLE travail.hab_phyto
(
  id_hab integer,
  "surf_%_hab" real,
  id_statut integer,
  association_veg character varying(150),
  id_code_cb integer,
  id_natura integer,
  id_phyto integer,
  id_cahab integer,
  id_eunis integer,
  id_hab_phyto serial NOT NULL,
  CONSTRAINT pk_hab_phyto PRIMARY KEY (id_hab_phyto),
  CONSTRAINT fk_cahab FOREIGN KEY (id_cahab)
      REFERENCES cahab (id_cahab) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb FOREIGN KEY (id_code_cb)
      REFERENCES corine_biotopes (id_code_cb) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_hab FOREIGN KEY (id_hab)
      REFERENCES habitat (id_hab) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_natura FOREIGN KEY (id_natura)
      REFERENCES natura_2000 (id_natura_2000) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_phyto FOREIGN KEY (id_phyto)
      REFERENCES code_phyto (id_code_phyto) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT id_eunis FOREIGN KEY (id_eunis)
      REFERENCES eunis (id_eunis) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE travail.hab_phyto OWNER TO postgres;

CREATE OR REPLACE FUNCTION travail.trig_maj_hab()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO travail.hab_phyto (id_hab)
VALUES (NEW.id_hab);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION travail.trig_maj_hab() OWNER TO postgres;

CREATE OR REPLACE FUNCTION trig_copy_id_hab()
  RETURNS trigger AS
$BODY$
BEGIN
    insert into hab_phyto (id_hab) values (NEW.id_hab);
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION trig_copy_id_hab() OWNER TO postgres;

CREATE TRIGGER copy_id_hab
  AFTER INSERT OR UPDATE
  ON habitat
  FOR EACH ROW
  EXECUTE PROCEDURE trig_copy_id_hab();

CREATE OR REPLACE FUNCTION travail.trig_maj_hab()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO travail.hab_phyto (id_hab)
VALUES (NEW.id_hab);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION travail.trig_maj_hab() OWNER TO postgres;

CREATE TRIGGER trig_maj_id_hab
  AFTER INSERT OR UPDATE
  ON travail.habitat
  FOR EACH ROW
  EXECUTE PROCEDURE travail.trig_maj_hab();