CREATE OR REPLACE FUNCTION outils.idx(anyarray, anyelement)
  RETURNS integer AS
$BODY$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION outils.idx(anyarray, anyelement) OWNER TO dba;

CREATE OR REPLACE FUNCTION md.liste_nom_auteur(text)
  RETURNS text AS
$BODY$
DECLARE
	myrec RECORD;
	var_liste_code_observateur ALIAS FOR $1;
		var_liste_nom_observateur character varying DEFAULT '';
BEGIN
FOR myrec IN SELECT nom||' '||prenom AS nom_prenom FROM md.personne WHERE code_personne = ANY(string_to_array(var_liste_code_observateur, '&')) ORDER BY outils.idx(string_to_array(var_liste_code_observateur, '&'), code_personne::text) ASC
	LOOP
		var_liste_nom_observateur:=var_liste_nom_observateur||', '||myrec.nom_prenom;
	END LOOP;
RETURN substring(var_liste_nom_observateur FROM 3 FOR (length(var_liste_nom_observateur))::integer);
--RETURN var_liste_nom_observateur||'-'||(length(var_liste_nom_observateur)-3)::text;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION md.liste_nom_auteur(text) OWNER TO dba;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO public;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO dba;
GRANT EXECUTE ON FUNCTION md.liste_nom_auteur(text) TO rss;

CREATE OR REPLACE FUNCTION md.creation_modification_observateur()
  RETURNS TRIGGER AS
$BODY$
DECLARE
	var_ancien_code_observateur character varying;
	var_ancien_login character varying;
	var_password character varying;
	var_ancien_role md.enum_role;
	var_nouveau_role md.enum_role;
	myrec RECORD;
 
BEGIN
	IF (TG_OP = 'DELETE') THEN -- mise à jour d'un utilisateur (mot de passe et mail non nuls et role différent de observ
		EXECUTE 
			$req$
				DROP VIEW IF EXISTS saisie.$req$||lower(OLD.code_personne)||$req$_saisie_observation;
			$req$;
	END IF;
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		IF (NEW.role<>'observ') THEN
			EXECUTE 
			$req$
				DROP VIEW IF EXISTS saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation;
				CREATE OR REPLACE VIEW saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation AS
				SELECT * FROM saisie.saisie_observation WHERE lower(saisie_observation.numerisateur)::text = lower('$req$||NEW.code_personne||$req$')::text;
				GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation TO sicen_gr_amateur;
				GRANT UPDATE (date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, referentiel_taxonomique, effectif_min, effectif_max, type_effectif, phenologie, id_waypoint, longitude, latitude, elevation, systeme_coordonnees, unite, localisation, observateur, numerisateur, structure, remarque_obs, id_element_paysage, date_ventilation, geometrie, code_insee, id_lieu_dit, id_etude, id_protocole, effectif_textuel, diffusable, "precision", effectif, url_photo, commentaire_photo) ON TABLE saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation TO sicen_gr_amateur;
			$req$;
		RAISE INFO 'vue créée pour %', NEW.code_personne;
 
		RAISE INFO 'Création des règles d''accès à ces vues pour =%', NEW.email;		
		EXECUTE 
			$req$
				-- règle lors de l'ajout de données à la vue : INSERT
				CREATE OR REPLACE RULE insert_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON INSERT TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				INSERT INTO saisie.saisie_observation(date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, referentiel_taxonomique, effectif_min, effectif_max, type_effectif, phenologie, id_waypoint, longitude, latitude, elevation, systeme_coordonnees, unite, localisation, observateur, numerisateur, validateur, structure, remarque_obs, id_element_paysage, date_ventilation, geometrie, code_insee, id_lieu_dit, statut_validation, id_etude, id_protocole, effectif_textuel, diffusable, "precision", effectif, url_photo, commentaire_photo)
				VALUES (NEW.date_obs, NEW.date_debut_obs, NEW.date_fin_obs, NEW.date_textuelle, NEW.regne, NEW.nom_vern, NEW.nom_complet, NEW.cd_nom, NEW.referentiel_taxonomique, NEW.effectif_min, NEW.effectif_max, NEW.type_effectif, NEW.phenologie, NEW.id_waypoint, NEW.longitude, NEW.latitude, NEW.elevation, NEW.systeme_coordonnees, NEW.unite, NEW.localisation, NEW.observateur, NEW.numerisateur, NEW.validateur, NEW.structure, NEW.remarque_obs, NEW.id_element_paysage, NEW.date_ventilation, NEW.geometrie, NEW.code_insee, NEW.id_lieu_dit, NEW.statut_validation, NEW.id_etude, NEW.id_protocole, NEW.effectif_textuel, NEW.diffusable, NEW."precision", NEW.effectif, NEW.url_photo, NEW.commentaire_photo);
 
				-- règle lors de la mise à jour de données de la vue : UPDATE
				CREATE OR REPLACE RULE update_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON UPDATE TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				UPDATE saisie.saisie_observation
				SET date_obs = NEW.date_obs, date_debut_obs = NEW.date_debut_obs, date_fin_obs = NEW.date_fin_obs, date_textuelle = NEW.date_textuelle, regne = NEW.regne, nom_vern = NEW.nom_vern, nom_complet = NEW.nom_complet, cd_nom = NEW.cd_nom, referentiel_taxonomique = NEW.referentiel_taxonomique, effectif_min = NEW.effectif_min, effectif_max = NEW.effectif_max, type_effectif = NEW.type_effectif, phenologie = NEW.phenologie, id_waypoint = NEW.id_waypoint, longitude = NEW.longitude, latitude = NEW.latitude, elevation = NEW.elevation, systeme_coordonnees = NEW.systeme_coordonnees, unite = NEW.unite, localisation = NEW.localisation, observateur = NEW.observateur, numerisateur = NEW.numerisateur, validateur = NEW.validateur, structure = NEW.structure, remarque_obs = NEW.remarque_obs, id_element_paysage = NEW.id_element_paysage, date_ventilation = NEW.date_ventilation, geometrie = NEW.geometrie, code_insee = NEW.code_insee, id_lieu_dit = NEW.id_lieu_dit, statut_validation = NEW.statut_validation, id_etude = NEW.id_etude, id_protocole = NEW.id_protocole, effectif_textuel = NEW.effectif_textuel, diffusable = NEW.diffusable, "precision" = NEW."precision", effectif = NEW.effectif, url_photo = NEW.url_photo, commentaire_photo = NEW.commentaire_photo
				WHERE id_obs = OLD.id_obs;
 
				-- règle lors de la suppression d'une données de la vue : DELETE
				CREATE RULE delete_$req$||lower(NEW.code_personne)||$req$_saisie_observation AS ON DELETE TO saisie.$req$||lower(NEW.code_personne)||$req$_saisie_observation
				DO INSTEAD
				DELETE FROM saisie.saisie_observation
				WHERE id_obs = OLD.id_obs;
			$req$;
 
		END IF;
	END IF;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION md.creation_modification_observateur() OWNER TO dba;
GRANT EXECUTE ON FUNCTION md.creation_modification_observateur() TO public;
GRANT EXECUTE ON FUNCTION md.creation_modification_observateur() TO dba;

CREATE TRIGGER md_creation_observateur
  AFTER INSERT OR UPDATE OR DELETE
  ON md.personne
  FOR EACH ROW
  EXECUTE PROCEDURE md.creation_modification_observateur();