QGIS » ASGARD

--------------------------------------------

------ 3 - CREATION DES EVENT TRIGGERS ------

--------------------------------------------

------ 3.1 - EVENT TRIGGER SUR ALTER SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_alter_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_alter_schema() RETURNS event_trigger

    LANGUAGE plpgsql

    AS $BODY$

/* OBJET : Fonction exécutée par l'event trigger asgard_on_alter_schema qui

           répercute dans la table z_asgard_admin.gestion_schema (via la vue

           z_asgard.gestion_schema_etr) les modifications de noms

           et propriétaires des schémas réalisées par des commandes

           ALTER SCHEMA directes.

DECLENCHEMENT : ON DDL COMMAND END.

CONDITION : WHEN TAG IN ('ALTER SCHEMA') */

DECLARE

    obj record ;

    e_mssg text ;

    e_hint text ;

    e_detl text ;

BEGIN

    ------ CONTROLES DES PRIVILEGES ------

    IF NOT has_schema_privilege('z_asgard', 'USAGE')

    THEN

        RAISE EXCEPTION 'EAS1. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

    IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')

             OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')

    THEN

        RAISE EXCEPTION 'EAS2. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

	FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()

                    WHERE object_type = 'schema'

    LOOP

        ------ RENAME ------

        UPDATE z_asgard.gestion_schema_etr

            SET nom_schema = replace(obj.object_identity, '"', ''),

                ctrl = ARRAY['RENAME', 'x7-A;#rzo']

            WHERE oid_schema = obj.objid

                AND NOT quote_ident(nom_schema) = obj.object_identity ;

        IF FOUND

        THEN

            RAISE NOTICE '... Le nom du schéma % a été mis à jour dans la table de gestion.',  replace(obj.object_identity, '"', '') ;

        END IF ;

        ------ OWNER TO ------

        UPDATE z_asgard.gestion_schema_etr

            SET (producteur, oid_producteur, ctrl) = (

                SELECT

                    replace(nspowner::regrole::text, '"', ''),

                    nspowner,

                    ARRAY['OWNER', 'x7-A;#rzo']

                    FROM pg_catalog.pg_namespace

                    WHERE obj.objid = pg_namespace.oid

                )

			WHERE oid_schema = obj.objid

    			AND NOT oid_producteur = (

             		SELECT nspowner

                  	FROM pg_catalog.pg_namespace

                 	 WHERE obj.objid = pg_namespace.oid

             		) ;

        IF FOUND

        THEN

            RAISE NOTICE '... Le producteur du schéma % a été mis à jour dans la table de gestion.',  replace(obj.object_identity, '"', '') ;

        END IF ;

    END LOOP ;

EXCEPTION WHEN OTHERS THEN

    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,

                            e_hint = PG_EXCEPTION_HINT,

                            e_detl = PG_EXCEPTION_DETAIL ;

    RAISE EXCEPTION 'EAS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'

         USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' ||  e_detl END,

               HINT = e_hint ;

END

$BODY$ ;

------ 3.2 - EVENT TRIGGER SUR CREATE SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_create_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_create_schema() RETURNS event_trigger

    LANGUAGE plpgsql

    AS $BODY$

/* OBJET : Fonction exécutée par l'event trigger asgard_on_create_schema qui

           répercute dans la table z_asgard_admin.gestion_schema (via la vue

           z_asgard.gestion_schema_etr) les créations de schémas

           réalisées par des commandes CREATE SCHEMA directes.

DECLENCHEMENT : ON DDL COMMAND END.

CONDITION : WHEN TAG IN ('CREATE SCHEMA') */

DECLARE

    obj record ;

    e_mssg text ;

    e_hint text ;

    e_detl text ;

BEGIN

    ------ CONTROLES DES PRIVILEGES ------

    IF NOT has_schema_privilege('z_asgard', 'USAGE')

    THEN

        RAISE EXCEPTION 'ECS1. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

    IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')

            OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'INSERT')

            OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')

    THEN

        RAISE EXCEPTION 'ECS2. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

	FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()

                    WHERE object_type = 'schema'

    LOOP

        ------ SCHEMA PRE-ENREGISTRE DANS GESTION_SCHEMA ------

        UPDATE z_asgard.gestion_schema_etr

            SET (oid_schema, producteur, oid_producteur, creation, ctrl) = (

                SELECT

                    obj.objid,

                    replace(nspowner::regrole::text, '"', ''),

                    nspowner,

                    true,

                    ARRAY['CREATE', 'x7-A;#rzo']

                    FROM pg_catalog.pg_namespace

                    WHERE obj.objid = pg_namespace.oid

                )

            WHERE quote_ident(nom_schema) = obj.object_identity

                AND NOT creation  ; -- creation vaut true si et seulement si la création a été initiée via la table

                                    -- de gestion dans ce cas, il n'est pas nécessaire de réintervenir dessus

        IF FOUND

        THEN

            RAISE NOTICE '... Le schéma % apparaît désormais comme "créé" dans la table de gestion.',  replace(obj.object_identity, '"', '') ;

        ------ SCHEMA NON REPERTORIE DANS GESTION_SCHEMA ------

        ELSIF NOT obj.object_identity IN (SELECT quote_ident(nom_schema) FROM z_asgard.gestion_schema_etr)

        THEN

            INSERT INTO z_asgard.gestion_schema_etr (oid_schema, nom_schema, producteur, oid_producteur, creation, ctrl)(

                SELECT

                    obj.objid,

                    replace(obj.object_identity, '"', ''),

                    replace(nspowner::regrole::text, '"', ''),

                    nspowner,

                    true,

                    ARRAY['CREATE', 'x7-A;#rzo']

                    FROM pg_catalog.pg_namespace

                    WHERE obj.objid = pg_namespace.oid

                ) ;

            RAISE NOTICE '... Le schéma % a été enregistré dans la table de gestion.',  replace(obj.object_identity, '"', '') ;

        END IF ;

	END LOOP ;

EXCEPTION WHEN OTHERS THEN

    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,

                            e_hint = PG_EXCEPTION_HINT,

                            e_detl = PG_EXCEPTION_DETAIL ;

    RAISE EXCEPTION 'ECS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'

         USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' ||  e_detl END,

               HINT = e_hint ;

END

$BODY$ ;

------ 3.3 - EVENT TRIGGER SUR DROP SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_drop_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_drop_schema() RETURNS event_trigger

    LANGUAGE plpgsql

    AS $BODY$

/* OBJET : Fonction exécutée par l'event trigger asgard_on_drop_schema qui

           répercute dans la table z_asgard_admin.gestion_schema (via la vue

           z_asgard.gestion_schema_etr) les suppressions de schémas

           réalisées par des commandes DROP SCHEMA directes.

DECLENCHEMENT : ON SQL DROP.

CONDITION : WHEN TAG IN ('DROP SCHEMA') */

DECLARE

	obj record ;

    e_mssg text ;

    e_hint text ;

    e_detl text ;

BEGIN

    ------ CONTROLES DES PRIVILEGES ------

    IF NOT has_schema_privilege('z_asgard', 'USAGE')

    THEN

        RAISE EXCEPTION 'EDS1. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

    IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')

            OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')

    THEN

        RAISE EXCEPTION 'EDS2. Echec.'

            USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;

    END IF ;

	FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()

                    WHERE object_type = 'schema'

    LOOP

        ------ ENREGISTREMENT DE LA SUPPRESSION ------

		UPDATE z_asgard.gestion_schema_etr

			SET (creation, oid_schema, ctrl) = (False, NULL, ARRAY['DROP', 'x7-A;#rzo'])

			WHERE quote_ident(nom_schema) = obj.object_identity ;    

		IF FOUND THEN

			RAISE NOTICE '... La suppression du schéma % a été enregistrée dans la table de gestion (creation = False).', replace(obj.object_identity, '"', '');

		END IF ;

	END LOOP ;

EXCEPTION WHEN OTHERS THEN

    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,

                            e_hint = PG_EXCEPTION_HINT,

                            e_detl = PG_EXCEPTION_DETAIL ;

    RAISE EXCEPTION 'EDS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'

         USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' ||  e_detl END,

               HINT = e_hint ;

END

$BODY$;

---------------------------------------

------ 4 - FONCTIONS UTILITAIRES ------

---------------------------------------

------ 4.6 - DEREFERENCEMENT D'UN SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_sortie_gestion_schema(text)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_sortie_gestion_schema(n_schema text)

    RETURNS text

    LANGUAGE plpgsql

    AS $_$

/* OBJET : Cette fonction permet de supprimer de la table de gestion un

           schéma existant (qui échappera alors aux mécanismes de

           gestion des droits), en outrepassant les règles qui veulent

           que seules les lignes avec creation valant false puisse

           être ciblées par des DELETE et que creation ne puisse être

           mis à false si le schéma existe.

ARGUMENTS :

- n_schema : nom d'un schéma présumé référencé dans le champ

             nom_schema de la table de gestion (sinon la fonction n'aura

             pas d'effet).

SORTIE : '__ DEREFERENCEMENT REUSSI.' si la requête s'est exécutée normalement. */

DECLARE

    e_mssg text ;

    e_detl text ;

    e_hint text ;

BEGIN

    UPDATE z_asgard.gestion_schema_etr

        SET ctrl = ARRAY['EXIT', 'x7-A;#rzo']

        WHERE nom_schema = n_schema ;

    DELETE FROM z_asgard.gestion_schema_etr

        WHERE nom_schema = n_schema ;

    RETURN '__ DEREFERENCEMENT REUSSI.' ;

EXCEPTION WHEN OTHERS THEN

    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,

                            e_hint = PG_EXCEPTION_HINT,

                            e_detl = PG_EXCEPTION_DETAIL ;

    RAISE EXCEPTION 'FSG0. Opération annulée. Anomalie lors du déréférencement de la table de gestion.'

         USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' ||  e_detl END,

               HINT = e_hint ;

END

$_$;

---------------------------------------------

------ 5 - TRIGGERS SUR GESTION_SCHEMA ------

---------------------------------------------

------ 5.1 - TRIGGER BEFORE ------

-- FUNCTION: z_asgard_admin.asgard_on_modify_gestion_schema_before()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_before() RETURNS trigger

    LANGUAGE plpgsql

    AS $BODY$

/* OBJET : Fonction exécutée par le trigger asgard_on_modify_gestion_schema_before,

           qui valide les informations saisies dans la table de gestion.

CIBLES : z_asgard_admin.gestion_schema.

PORTEE : FOR EACH ROW.

DECLENCHEMENT : BEFORE INSERT, UPDATE, DELETE.*/

DECLARE

    n_role text ;

BEGIN

    ------ SAISIE PAR UN UTILISATEUR NON HABILITE ------

    IF TG_OP = 'INSERT' AND NOT has_database_privilege(current_database(), 'CREATE')

    -- même si creation vaut faux, seul un rôle habilité à créer des

    -- schéma peut ajouter des lignes dans la table de gestion

    THEN

        RAISE EXCEPTION 'TB1. Vous devez être habilité à créer des schémas pour réaliser cette opération.' ;

    END IF ;

    ------ APPLICATION DES VALEURS PAR DEFAUT ------

    -- au tout début car de nombreux tests sont faits par la

    -- suite sur "NOT NEW.creation"

    IF TG_OP IN ('INSERT', 'UPDATE')

    THEN

        NEW.creation := coalesce(NEW.creation, False) ;

        NEW.nomenclature := coalesce(NEW.nomenclature, False) ;

    END IF ;

    ------ EFFACEMENT D'UN ENREGISTREMENT ------

    IF TG_OP = 'DELETE'

    THEN   

        -- on n'autorise pas l'effacement si creation vaut True

        -- avec une exception pour les commandes envoyées par la fonction

        -- de maintenance asgard_sortie_gestion_schema

        IF OLD.creation AND (OLD.ctrl[1] IS NULL OR NOT OLD.ctrl[1] = 'EXIT')

        THEN

            RAISE EXCEPTION 'TB2. Opération interdite (schéma %). L''effacement n''est autorisé que si creation vaut False.', OLD.nom_schema

                USING HINT = 'Pour déréférencer un schéma sans le supprimer, vous pouvez utiliser la fonction z_asgard_admin.asgard_sortie_gestion_schema.' ;

        END IF;

        -- on n'autorise pas l'effacement pour les schémas de la nomenclature

        IF OLD.nomenclature

        THEN

            RAISE EXCEPTION 'TB3. Opération interdite (schéma %). L''effacement n''est pas autorisé pour les schémas de la nomenclature nationale.', OLD.nom_schema

                USING HINT = 'Si vous tenez à supprimer définitivement cet enregistrement, basculez préalablement nomenclature sur False.' ;

        END IF ;

    END IF;

    ------ DE-CREATION D'UN SCHEMA ------

    IF TG_OP = 'UPDATE'

    THEN

        -- si bloc valait déjà d (schéma "mis à la corbeille")

        -- on exécute une commande de suppression du schéma. Toute autre modification sur

        -- la ligne est ignorée.

        IF OLD.bloc = 'd' AND OLD.creation AND NOT NEW.creation AND NEW.ctrl[2] IS NULL

                AND OLD.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)

        THEN

            EXECUTE 'DROP SCHEMA ' || quote_ident(OLD.nom_schema) || ' CASCADE' ;

            RAISE NOTICE '... Le schéma % a été supprimé.', OLD.nom_schema ;

            RETURN NULL ;

        -- sinon, on n'autorise creation à passer de true à false que si le schéma

        -- n'existe plus (permet notamment à l'event trigger qui gère les

        -- suppressions de mettre creation à false)

        ELSIF OLD.creation and NOT NEW.creation

                AND NEW.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)

        THEN

            RAISE EXCEPTION 'TB4. Opération interdite (schéma %). Le champ creation ne peut passer de Vrai à Faux si le schéma existe.', NEW.nom_schema

                USING HINT =  'Si vous supprimez physiquement le schéma avec la commande DROP SCHEMA, creation basculera sur False automatiquement.' ;

        END IF ;

    END IF ;

    IF TG_OP <> 'DELETE'

    THEN

        ------ PROHIBITION DE LA SAISIE MANUELLE DES OID ------

        -- vérifié grâce au champ ctrl

        IF NEW.ctrl[2] IS NULL

            OR NOT array_length(NEW.ctrl, 1) >= 2

            OR NEW.ctrl[1] IS NULL

            OR NOT NEW.ctrl[1] IN ('CREATE', 'RENAME', 'OWNER', 'DROP', 'SELF', 'EXIT')

            OR NOT NEW.ctrl[2] = 'x7-A;#rzo'

            -- ctrl NULL ou invalide

        THEN

            IF NEW.ctrl[1] = 'EXIT'

            THEN

                RAISE EXCEPTION 'TB17. Opération interdite (schéma %).', coalesce(NEW.nom_schema, '?')

                    USING HINT = 'Pour déréférencer un schéma, veuillez utiliser la fonction z_asgard_admin.asgard_sortie_gestion_schema.' ;

            END IF ;

            -- réinitialisation du champ ctrl, qui peut contenir des informations

            -- issues de commandes antérieures (dans ctrl[1])

            NEW.ctrl := ARRAY['MANUEL', NULL]::text[] ;

            IF TG_OP = 'INSERT' AND (

                    NEW.oid_producteur IS NOT NULL

                    OR NEW.oid_lecteur IS NOT NULL

                    OR NEW.oid_editeur IS NOT NULL

                    OR NEW.oid_schema IS NOT NULL

                    )

            -- cas d'un INSERT manuel pour lequel des OID ont été saisis

            -- on les remet à NULL

            THEN

                NEW.oid_producteur = NULL ;

                NEW.oid_editeur = NULL ;

                NEW.oid_lecteur = NULL ;

                NEW.oid_schema = NULL ;

            ELSIF TG_OP = 'UPDATE'

            THEN

                IF NOT coalesce(NEW.oid_producteur, -1) = coalesce(OLD.oid_producteur, -1)

                        OR NOT coalesce(NEW.oid_editeur, -1) = coalesce(OLD.oid_editeur, -1)

                        OR NOT coalesce(NEW.oid_lecteur, -1) = coalesce(OLD.oid_lecteur, -1)

                        OR NOT coalesce(NEW.oid_schema, -1) = coalesce(OLD.oid_schema, -1)

                -- cas d'un UPDATE avec modification des OID

                -- on les remet à OLD

                THEN

                    NEW.oid_producteur = OLD.oid_producteur ;

                    NEW.oid_editeur = OLD.oid_editeur ;

                    NEW.oid_lecteur = OLD.oid_lecteur ;

                    NEW.oid_schema = OLD.oid_schema ;

                END IF ;

            END IF ;                

        ELSE

            -- suppression du mot de passe de contrôle.

            -- ctrl[1] est par contre conservé - il sera utilisé

            -- par le trigger AFTER pour connaître l'opération

            -- à l'origine de son déclenchement.

            NEW.ctrl[2] := NULL ;

        END IF ;

        ------ REQUETES AUTO A IGNORER ------

        -- les remontées du trigger AFTER (SELF)

        -- sont exclues, car les contraintes ont déjà

        -- été validées (et pose problèmes avec les

        -- contrôles d'OID sur les UPDATE, car ceux-ci

        -- ne seront pas nécessairement déjà remplis) ;

        -- les requêtes EXIT de même, car c'est un

        -- pré-requis à la suppression qui ne fait

        -- que modifier le champ ctrl

        IF NEW.ctrl[1] IN ('SELF', 'EXIT')

        THEN

            -- aucune action

            RETURN NEW ;

        END IF ;

        ------ VERROUILLAGE DES CHAMPS LIES A LA NOMENCLATURE ------

        -- modifiables uniquement par l'ADL

        IF TG_OP = 'UPDATE'

        THEN

            IF (OLD.nomenclature OR NEW.nomenclature) AND NOT pg_has_role('g_admin', 'MEMBER') AND (

                    NOT coalesce(OLD.nomenclature, False) = coalesce(NEW.nomenclature, False)

                    OR NOT coalesce(OLD.niv1, '') = coalesce(NEW.niv1, '')

                    OR NOT coalesce(OLD.niv1_abr, '') = coalesce(NEW.niv1_abr, '')

                    OR NOT coalesce(OLD.niv2, '') = coalesce(NEW.niv2, '')

                    OR NOT coalesce(OLD.niv2_abr, '') = coalesce(NEW.niv2_abr, '')

                    OR NOT coalesce(OLD.nom_schema, '') = coalesce(NEW.nom_schema, '')

                    OR NOT coalesce(OLD.bloc, '') = coalesce(NEW.bloc, '')

                    )

            THEN

                RAISE EXCEPTION 'TB18. Opération interdite (schéma %).', NEW.nom_schema

                    USING HINT = 'Seuls les membres de g_admin sont habilités à modifier les champs nomenclature et - pour les schémas de la nomenclature - bloc, niv1, niv1_abr, niv2, niv2_abr et nom_schema.' ;

            END IF ;

        ELSIF TG_OP = 'INSERT'

        THEN

            IF NEW.nomenclature AND NOT pg_has_role('g_admin', 'MEMBER')

            THEN

                RAISE EXCEPTION 'TB19. Opération interdite (schéma %).', NEW.nom_schema

                    USING HINT = 'Seuls les membres de g_admin sont autorisés à ajouter des schémas à la nomenclature (nomenclature = True).' ;

            END IF ;

        END IF ;

        ------ NETTOYAGE DES CHAÎNES VIDES ------

        -- si l'utilisateur a entré des chaînes vides on met des NULL

        NEW.editeur := nullif(NEW.editeur, '') ;

        NEW.lecteur := nullif(NEW.lecteur, '') ;

        NEW.bloc := nullif(NEW.bloc, '') ;

        NEW.niv1 := nullif(NEW.niv1, '') ;

        NEW.niv1_abr := nullif(NEW.niv1_abr, '') ;

        NEW.niv2 := nullif(NEW.niv2, '') ;

        NEW.niv2_abr := nullif(NEW.niv2_abr, '') ;

        NEW.nom_schema := nullif(NEW.nom_schema, '') ;

        -- si producteur est vide on met par défaut g_admin

        NEW.producteur := coalesce(nullif(NEW.producteur, ''), 'g_admin') ;

        ------ NETTOYAGE DES CHAMPS OID ------

        -- pour les rôles de lecteur et éditeur,

        -- si le champ de nom est vidé par l'utilisateur,

        -- on vide en conséquence l'OID

        IF NEW.editeur IS NULL

        THEN

            NEW.oid_editeur := NULL ;

        END IF ;

        IF NEW.lecteur IS NULL

        THEN

            NEW.oid_lecteur := NULL ;

        END IF ;

        -- si le schéma n'est pas créé, on s'assure que les champs

        -- d'OID restent vides

        -- à noter que l'event trigger sur DROP SCHEMA vide

        -- déjà le champ oid_schema

        IF NOT NEW.creation

        THEN

            NEW.oid_schema := NULL ;

            NEW.oid_lecteur := NULL ;

            NEW.oid_editeur := NULL ;

            NEW.oid_producteur := NULL ;

        END IF ;

        ------ VALIDITE DES NOMS DE ROLES ------

        -- dans le cas d'un schéma pré-existant, on s'assure que les rôles qui

        -- ne changent pas sont toujours valides (qu'ils existent et que le nom

        -- n'a pas été modifié entre temps)

        -- si tel est le cas, on les met à jour et on le note dans

        -- ctrl, pour que le trigger AFTER sache qu'il ne s'agit

        -- pas réellement de nouveaux rôles sur lesquels les droits

        -- devraient être réappliqués

        IF TG_OP = 'UPDATE' AND NEW.creation

        THEN

            -- producteur

            IF OLD.creation AND OLD.producteur = NEW.producteur

            THEN

                SELECT rolname INTO n_role

                    FROM pg_catalog.pg_roles

                    WHERE pg_roles.oid = NEW.oid_producteur ;

                IF NOT FOUND

                -- le rôle producteur n'existe pas

                THEN

                    -- cas invraisemblable, car un rôle ne peut pas être

                    -- supprimé alors qu'il est propriétaire d'un schéma, et la

                    -- commande ALTER SCHEMA OWNER TO aurait été interceptée

                    -- mais, s'il advient, on repart du propriétaire

                    -- renseigné dans pg_namespace

                    SELECT replace(nspowner::regrole::text, '"', ''), nspowner

                        INTO NEW.producteur, NEW.oid_producteur

                        FROM pg_catalog.pg_namespace

                        WHERE pg_namespace.oid = NEW.oid_schema ;

                    RAISE NOTICE '[table de gestion] ANOMALIE. Schéma %. L''OID actuellement renseigné pour le producteur est invalide. Poursuite avec l''OID du propriétaire courant du schéma.', NEW.nom_schema ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN producteur') ;

                ELSIF NOT n_role = NEW.producteur

                -- libellé obsolète du producteur

                THEN

                    NEW.producteur := n_role ;

                    RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle producteur, renommé entre temps.', NEW.nom_schema

                        USING DETAIL = 'Ancien nom "' || OLD.producteur || '", nouveau nom "' || NEW.producteur || '".' ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN producteur') ;

                END IF ; 

            END IF ;

            -- éditeur

            IF OLD.creation AND OLD.editeur = NEW.editeur

                    AND NOT NEW.editeur = 'public'

            THEN

                SELECT rolname INTO n_role

                    FROM pg_catalog.pg_roles

                    WHERE pg_roles.oid = NEW.oid_editeur ;

                IF NOT FOUND

                -- le rôle éditeur n'existe pas

                THEN

                    NEW.editeur := NULL ;

                    NEW.oid_editeur := NULL ;

                    RAISE NOTICE '[table de gestion] Schéma %. Le rôle éditeur n''existant plus, il est déréférencé.', NEW.nom_schema

                        USING DETAIL = 'Ancien nom "' || OLD.editeur || '".' ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN editeur') ;

                ELSIF NOT n_role = NEW.editeur

                -- libellé obsolète de l'éditeur

                THEN

                    NEW.editeur := n_role ;

                    RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle éditeur, renommé entre temps.', NEW.nom_schema

                        USING DETAIL = 'Ancien nom "' || OLD.editeur || '", nouveau nom "' || NEW.editeur || '".' ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN editeur') ;

                END IF ; 

            END IF ;

            -- lecteur

            IF OLD.creation AND OLD.lecteur = NEW.lecteur

                    AND NOT NEW.lecteur = 'public'

            THEN

                SELECT rolname INTO n_role

                    FROM pg_catalog.pg_roles

                    WHERE pg_roles.oid = NEW.oid_lecteur ;

                IF NOT FOUND

                -- le rôle lecteur n'existe pas

                THEN

                    NEW.lecteur := NULL ;

                    NEW.oid_lecteur := NULL ;

                    RAISE NOTICE '[table de gestion] Schéma %. Le rôle lecteur n''existant plus, il est déréférencé.', NEW.nom_schema

                        USING DETAIL = 'Ancien nom "' || OLD.lecteur || '".' ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN lecteur') ;

                ELSIF NOT n_role = NEW.lecteur

                -- libellé obsolète du lecteur

                THEN

                    NEW.lecteur := n_role ;

                    RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle lecteur, renommé entre temps.', NEW.nom_schema

                        USING DETAIL = 'Ancien nom "' || OLD.lecteur || '", nouveau nom "' || NEW.lecteur || '".' ;

                    NEW.ctrl := array_append(NEW.ctrl, 'CLEAN lecteur') ;

                END IF ; 

            END IF ;    

        END IF ;

        ------ NON RESPECT DES CONTRAINTES ------

        -- non nullité de nom_schema

        IF NEW.nom_schema IS NULL

        THEN

            RAISE EXCEPTION 'TB8. Saisie incorrecte. Le nom du schéma doit être renseigné (champ nom_schema).' ;

        END IF ;

        -- unicité de nom_schema

        -- -> contrôlé après les manipulations sur les blocs de

        -- la partie suivante.

        -- unicité de oid_schema

        IF TG_OP = 'INSERT' AND NEW.oid_schema IN (SELECT gestion_schema_etr.oid_schema FROM z_asgard.gestion_schema_etr

                                                       WHERE gestion_schema_etr.oid_schema IS NOT NULL)

        THEN

            RAISE EXCEPTION 'TB11. Saisie incorrecte (schéma %). Un schéma de même OID est déjà répertorié dans la table de gestion.', NEW.nom_schema ;

        ELSIF TG_OP = 'UPDATE'

        THEN

            -- cas (très hypothétique) d'une modification d'OID

            IF NOT coalesce(NEW.oid_schema, -1) = coalesce(OLD.oid_schema, -1)

                    AND NEW.oid_schema IN (SELECT gestion_schema_etr.oid_schema FROM z_asgard.gestion_schema_etr

                                                       WHERE gestion_schema_etr.oid_schema IS NOT NULL)

            THEN

                RAISE EXCEPTION 'TB12. Saisie incorrecte (schéma %). Un schéma de même OID est déjà répertorié dans la table de gestion.', NEW.nom_schema ;

            END IF ;

        END IF ;

        -- non répétition des rôles

        IF NOT ((NEW.oid_lecteur IS NULL OR NOT NEW.oid_lecteur = NEW.oid_producteur)

                AND (NEW.oid_editeur IS NULL OR NOT NEW.oid_editeur = NEW.oid_producteur)

                AND (NEW.oid_lecteur IS NULL OR NEW.oid_editeur IS NULL OR NOT NEW.oid_lecteur = NEW.oid_editeur))

        THEN

            RAISE EXCEPTION 'TB13. Saisie incorrecte (schéma %). Les rôles producteur, lecteur et éditeur doivent être distincts.', NEW.nom_schema ;

        END IF ;

    END IF ;

    ------ COHERENCE BLOC/NOM DU SCHEMA ------

    IF TG_OP IN ('INSERT', 'UPDATE')

    THEN

        IF NEW.bloc IS NULL AND NEW.nom_schema ~ '^[a-z]_'

        -- si bloc est NULL, mais que le nom du schéma

        -- comporte un préfixe, on met ce préfixe dans bloc

        THEN

            NEW.bloc := substring(NEW.nom_schema, '^([a-z])_') ;

            RAISE NOTICE '[table de gestion] Mise à jour du bloc pour le schéma %.', NEW.nom_schema || ' (' || NEW.bloc || ')' ;

        ELSIF NEW.bloc IS NULL

        -- si bloc est NULL, et que (sous-entendu) le nom du schéma ne

        -- respecte pas la nomenclature, on avertit l'utilisateur

        THEN            

            RAISE NOTICE '[table de gestion] Le nom du schéma % ne respecte pas la nomenclature.', NEW.nom_schema

                USING HINT = 'Si vous saisissez un préfixe dans le champ bloc, il sera automatiquement ajouté au nom du schéma.' ;

        ELSIF NOT NEW.nom_schema ~ ('^'|| NEW.bloc || '_') AND NOT NEW.bloc = 'd'

        -- le bloc est renseigné mais le nom du schéma ne correspond pas

        -- (et il ne s'agit pas d'un schéma mis à la corbeille) :

        -- si le nom est de la forme 'a_...', alors :

        -- - dans le cas d'un UPDATE avec modification du nom

        -- du schéma et pas du bloc, on se fie au nom du schéma

        -- et on change le bloc ;

        -- - si bloc n'est pas une lettre, on renvoie une erreur ;

        -- - dans les autres cas, on se fie au bloc et change le

        -- préfixe.

        -- Si le nom ne comporte pas de préfixe :

        -- - si le bloc est une lettre, on l'ajoute au début du

        -- nom (sans doubler l'underscore, si le nom commençait par

        -- un underscore) ;

        -- - sinon on renvoie une erreur.

        THEN

            IF NEW.nom_schema ~ '^([a-z])?_'

            THEN

                IF TG_OP = 'UPDATE'

                THEN

                    IF NOT NEW.nom_schema = OLD.nom_schema AND NEW.bloc = OLD.bloc

                    THEN

                        NEW.bloc := substring(NEW.nom_schema, '^([a-z])_') ;

                        RAISE NOTICE '[table de gestion] Mise à jour du bloc pour le schéma %.', NEW.nom_schema || ' (' || NEW.bloc || ')' ;

                    ELSIF NOT NEW.bloc ~ '^[a-z]$'

                    THEN

                        RAISE EXCEPTION 'TB14. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;

                    ELSE

                        NEW.nom_schema := regexp_replace(NEW.nom_schema, '^([a-z])?_', NEW.bloc || '_') ;

                        RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;

                    END IF ;

                ELSIF NOT NEW.bloc ~ '^[a-z]$'

                THEN

                    RAISE EXCEPTION 'TB15. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;

                ELSE

                    NEW.nom_schema := regexp_replace(NEW.nom_schema, '^([a-z])?_', NEW.bloc || '_') ;

                    RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;

                END IF ;

            ELSIF NOT NEW.bloc ~ '^[a-z]$'

            THEN

                RAISE EXCEPTION 'TB16. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;

            ELSE

                NEW.nom_schema := NEW.bloc || '_' || NEW.nom_schema ;

                RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;

            END IF ;

            -- le trigger AFTER se chargera de renommer physiquement le

            -- schéma d'autant que de besoin

        END IF ;

    END IF ;

    ------ NON RESPECT DES CONTRAINTES (SUITE) ------

    -- unicité de nom_schema

    IF TG_OP IN ('INSERT', 'UPDATE')

    THEN

        IF TG_OP = 'INSERT' AND NEW.nom_schema IN (SELECT gestion_schema_etr.nom_schema FROM z_asgard.gestion_schema_etr)

        THEN

            RAISE EXCEPTION 'TB9. Saisie incorrecte (schéma %). Un schéma de même nom est déjà répertorié dans la table de gestion.', NEW.nom_schema ;

        ELSIF TG_OP = 'UPDATE'

        THEN

            -- cas d'un changement de nom

            IF NOT NEW.nom_schema = OLD.nom_schema

                   AND NEW.nom_schema IN (SELECT gestion_schema_etr.nom_schema FROM z_asgard.gestion_schema_etr)

            THEN 

                RAISE EXCEPTION 'TB10. Saisie incorrecte (schéma %). Un schéma de même nom est déjà répertorié dans la table de gestion.', NEW.nom_schema ;

            END IF ;

        END IF ;

    END IF ;

    ------ MISE À LA CORBEILLE ------

    -- notification de l'utilisateur

    IF TG_OP = 'UPDATE'

    THEN

        -- schéma existant dont bloc bascule sur 'd'

        -- ou schéma créé par bascule de creation sur True dans bloc vaut 'd'

        IF NEW.creation AND NEW.bloc = 'd' AND (NOT OLD.bloc = 'd' OR OLD.bloc IS NULL)

                OR NEW.creation AND NOT OLD.creation AND NEW.bloc = 'd'

        THEN

            RAISE NOTICE '[table de gestion] Le schéma % a été mis à la corbeille (bloc = ''d'').', NEW.nom_schema

                USING HINT = 'Si vous basculez creation sur False, le schéma et son contenu seront automatiquement supprimés.' ;

        -- restauration

        ELSIF NEW.creation AND OLD.bloc = 'd' AND (NOT NEW.bloc = 'd' OR NEW.bloc IS NULL)

        THEN

            RAISE NOTICE '[table de gestion] Le schéma % a été retiré de la corbeille (bloc ne vaut plus ''d'').', NEW.nom_schema ;

        END IF ;

    ELSIF TG_OP = 'INSERT'

    THEN

        -- nouveau schéma dont bloc vaut 'd'

        IF NEW.creation AND NEW.bloc = 'd'

        THEN

            RAISE NOTICE '[table de gestion] Le schéma % a été mis à la corbeille (bloc = ''d'').', NEW.nom_schema

                USING HINT = 'Si vous basculez creation sur False, le schéma et son contenu seront automatiquement supprimés.' ;  

        END IF ;

    END IF ;

    ------ RETURN ------

	IF TG_OP IN ('UPDATE', 'INSERT')

    THEN

        RETURN NEW ;

    ELSIF TG_OP = 'DELETE'

    THEN

        RETURN OLD ;

    END IF ;

END

$BODY$ ;

------ 5.2 - TRIGGER AFTER ------

-- FUNCTION: z_asgard_admin.asgard_on_modify_gestion_schema_after()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_after() RETURNS trigger

    LANGUAGE plpgsql

    AS $BODY$

/* OBJET : Fonction exécutée par le trigger asgard_on_modify_gestion_schema_after,

           qui répercute physiquement les modifications de la table de gestion.

CIBLES : z_asgard_admin.gestion_schema.

PORTEE : FOR EACH ROW.

DECLENCHEMENT : AFTER INSERT OR UPDATE.*/

DECLARE

    utilisateur text ;

    createur text ;

    administrateur text ;

    e_mssg text ;

    e_hint text ;

    e_detl text ;

    b_superuser boolean ;

    b_test boolean ;

    l_commande text[] ;

    c text ;

    c_reverse text ;

    a_producteur text ;

    a_editeur text ;

    a_lecteur text ;

    n int ;

BEGIN

    ------ REQUETES AUTO A IGNORER ------

    -- les remontées du trigger lui-même (SELF),

    -- ainsi que des event triggers sur les

    -- suppressions de schémas (DROP), n'appellent

    -- aucune action, elles sont donc exclues dès

    -- le départ

    -- les remontées des changements de noms sont

    -- conservées, pour le cas où la mise en

    -- cohérence avec "bloc" aurait conduit à une

    -- modification du nom par le trigger BEFORE

    -- (géré au point suivant)

    -- les remontées des créations et changements

    -- de propriétaire (CREATE et OWNER) appellent

    -- des opérations sur les droits plus lourdes

    -- qui ne permettent pas de les exclure en

    -- amont

    IF NEW.ctrl[1] IN ('SELF', 'DROP')

    THEN

        -- aucune action

        RETURN NULL ;

    END IF ;

    ------ MANIPULATIONS PREALABLES ------

    utilisateur := current_user ;

    -- si besoin pour les futures opérations sur les rôles,

    -- récupération du nom d'un rôle dont current_user est membre

    -- et qui a l'attribut CREATEROLE. Autant que possible, la

    -- requête renvoie current_user lui-même. On exclut d'office les

    -- rôles NOINHERIT qui ne pourront pas avoir simultanément les

    -- droits du propriétaire de NEW et OLD.producteur

    SELECT rolname INTO createur FROM pg_roles

        WHERE pg_has_role(rolname, 'MEMBER') AND rolcreaterole AND rolinherit

        ORDER BY rolname = current_user DESC ;

    IF TG_OP = 'UPDATE'

    THEN

        -- la validité de OLD.producteur n'ayant

        -- pas été contrôlée par le trigger BEFORE,

        -- on le fait maintenant

        SELECT rolname INTO a_producteur

            FROM pg_catalog.pg_roles

            WHERE pg_roles.oid = OLD.oid_producteur ;

        -- pour la suite, on emploira toujours

        -- a_producteur à la place de OLD.producteur

        -- pour les opérations sur les droits.

        -- Il est réputé non NULL pour un schéma

        -- pré-existant (OLD.creation vaut True),

        -- dans la mesure où un rôle ne peut être

        -- supprimé s'il est propriétaire d'un

        -- schéma et où tous les changements de

        -- propriétaires sont remontés par event

        -- triggers (+ contrôles pour assurer la

        -- non-modification manuelle des OID).

        IF NOT FOUND AND OLD.creation AND NOT 'CLEAN producteur' = ANY(NEW.ctrl)

        THEN

            RAISE NOTICE '[table de gestion] ANOMALIE. Schéma %. L''OID actuellement renseigné pour le producteur dans la table de gestion est invalide. Poursuite avec l''OID du propriétaire courant du schéma.', OLD.nom_schema ;

            SELECT replace(nspowner::regrole::text, '"', '') INTO a_producteur

                FROM pg_catalog.pg_namespace

                WHERE pg_namespace.oid = NEW.oid_schema ;

            IF NOT FOUND

            THEN

                RAISE EXCEPTION 'TA1. Anomalie critique (schéma %). Le propriétaire du schéma est introuvable.', OLD.nom_schema ;

            END IF ;

        END IF ;

    END IF ;

    ------ MISE EN APPLICATION D'UN CHANGEMENT DE NOM DE SCHEMA ------

    IF NOT NEW.oid_schema::regnamespace::text = quote_ident(NEW.nom_schema)