Le portail francophone de la géomatique

CREATE OR REPLACE VIEW public."001_Session_L"
 AS
 SELECT row_number() OVER ()::integer AS gid,
    session.id AS "idSess",
    polyligne.id AS "idPolyli",
    commentaire_texte.id AS "idCmtTxt",
    affaire.num_affaire AS "Num_aff",
    session.nom_session AS "Nom_sess",
    session.date_export::date AS "Date_exp",
    polyligne.geometrie_qgis_2154,
    polyligne.moins_bonne_precision AS "Preci_inf",
    polyligne.precision_moyenne AS "Preci_moy",
    polyligne.altitude_moyenne AS "Alt_moy",
    polyligne.longueur AS "Longueur",
    polyligne.date AS "Date_trace",
    session.statut_validation AS "Stat_val",
    commentaire_texte.texte AS "Comment",
    session.photo AS "Photo",
    session.commentaire_vocal AS "Comm_audio",
    utilisateur.nom_utilisateur AS "Obs_nom"
   FROM affaire
     JOIN session ON affaire.num_affaire = session.affaire_ref_id
     JOIN utilisateur ON session.observateur_ref_id = utilisateur.id
     LEFT JOIN polyligne ON session.id = polyligne.session_ref_id
     LEFT JOIN commentaire_texte ON session.commentaire_texte_ref_id = commentaire_texte.id
  ORDER BY polyligne.id;

ALTER TABLE public."001_Session_L"
    OWNER TO karumbureau_rct;


CREATE TRIGGER "001_Session_L_update"
    INSTEAD OF UPDATE 
    ON public."001_Session_L"
    FOR EACH ROW
    EXECUTE PROCEDURE public."001_Session_L_update"();

CREATE FUNCTION public."001_Session_L_update"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
  p_new_primary_key INTEGER; -- pour stocker la nouvelle clé primaire de la table commentaire_texte
  p_new_foreign_key INTEGER; -- pour stocker la nouvelle clé primaire de la table utilisateur

BEGIN

  -- table session
  UPDATE session SET
       affaire_ref_id = NEW."Num_aff",
       nom_session = NEW."Nom_sess",
       statut_validation = NEW."Stat_val",
       photo = NEW."Photo",
       commentaire_vocal = NEW."Comm_audio"
    WHERE id = NEW."idSess";

  -- table utilisateur
  IF OLD."Obs_nom" <> NEW."Obs_nom" THEN
    SELECT id INTO p_new_foreign_key FROM utilisateur
      WHERE nom_utilisateur = NEW."Obs_nom";
    IF p_new_foreign_key IS NULL THEN
      -- si aucun nom ne correspond, ne rien mettre à jour
      RAISE EXCEPTION  'Nom incorrect : %', NEW."Obs_nom";
    END IF;
    UPDATE session SET observateur_ref_id = p_new_foreign_key
      WHERE id = NEW."idSess";
  END IF;

  -- table commentaire
    IF LENGTH(OLD."Comment") > 0 AND LENGTH(NEW."Comment") > 0 THEN
      UPDATE commentaire_texte SET
            texte = NEW."Comment"
        WHERE id = OLD."idCmtTxt";
    ELSIF LENGTH(OLD."Comment") > 0 THEN
      UPDATE session SET
            commentaire_texte_ref_id = NULL
        WHERE id = NEW."idSess";
      DELETE FROM commentaire_texte
        WHERE id = OLD."idCmtTxt";
    ELSE
      INSERT INTO commentaire_texte (id, texte)
        VALUES (nextval('commentaire_texte_id_seq'), NEW."Comment");
      SELECT id INTO p_new_primary_key FROM commentaire_texte
          WHERE texte = NEW."Comment";
      UPDATE session SET commentaire_texte_ref_id = p_new_primary_key
        WHERE id = NEW."idSess";
    END IF;

  -- table polyligne
  IF OLD."idPolyli" <> NEW."idPolyli" THEN
    UPDATE polyligne SET
         geometrie_qgis_2154 = NEW."geometrie_qgis_2154",
         geometrie_ewkt_4326 = st_AsEWKT(st_transform(NEW.geometrie_qgis_2154,4326)),
         moins_bonne_precision = NEW."Preci_inf",
         precision_moyenne = NEW."Preci_moy",
         altitude_moyenne = NEW."Alt_moy",
         longueur = st_length(NEW."geometrie_qgis_2154"),
         date = NEW."Date_trace"
      WHERE id = OLD."idPolyli";
  END IF;

RETURN NEW;
END;
$BODY$;