PostgreSQL propose une gamme de déclencheurs redoutablement puissants appelés les « event triggers ». Ce type de triggers permet de déclencher des opérations sur l’ensemble des instructions de définition de structure (DDL) comme :
- CREATE TABLE
- ALTER TABLE
- CREATE VIEW
- etc
La documentation sur les instructions prises en charge par les event triggers peut être trouvée ici.
Me trouvant confrontée à une demande très précise dans le domaine PostgreSQL, j’ai trouvé la solution dans l’utilisation des event triggers.
Dans le cas précis, les contraintes sont les suivantes :
- Insérer des données de la manière la plus normale du monde, sans application de fonction
- Les transformer à la volée à leur insertion dans la table-cible de l’insertion
- Pouvoir les modifier toujours en injectant des données non-transformées
- Pouvoir les effacer sur condition sans donnée transformée
- Pouvoir lire les données transformées, mais les avoir en lecture en données normales
- Utiliser l’OID d’une table pour identifier l’enregistrement en cours de manipulation sans avoir besoin de passer par la clé primaire de la table
Plusieurs concepts sont abordés dans cet article :
- Event trigger : action sur une instruction de définition de structure (DDL : Data Definition Language)
- Utilisation des données du dictionnaire de la base de données accédée
- Exécution d’instructions dynamiques dans une fonction écrite en PLpg/SQL (PostgreSQL SQL Procedural Language)
- Liaison d’un trigger à une vue pour lui permettre d’accepter des instructions de manipulation de données (DML : Data Manipulation Language)
Event trigger
Définition synthétique
Un event trigger est un déclencheur de base de données lié à un évènement. Un trigger « classique » est lié à un évènement sur une table. Dans le cas de l’event trigger, l’évènement est de nature DDL : CREATE TABLE, ALTER TABLE,… Il est possible de capter plusieurs types d’évènements : ceux avant exécution de l’instruction DDL (quasiment pas documentés et de fait peu connus), les plus courants ceux après exécution de l’instruction DDL, ceux sur suppression de structure et ceux sur réécriture de requêtes.
Cette définition n’est pas exhaustive, l’article n’étant pas destiné à remplacer la documentation de PostgreSQL. Pour une définition plus précise, se reporter à la documentation.
Event trigger en fin d’instruction DDL (ddl_command_end)
L’exemple qui suit, portera sur les évènements suivant l’instruction DDL exécutée.
Il est possible avec cet évènement de capter notamment l’évènement « CREATE TABLE »
Ce trigger lorsqu’il est déclenché donne accès à un tableau de variables de type record nommé pg_event_trigger_ddl_commands. Cette variable contient nombre d’information sur l’objet qui a été manipulé par l’instruction DDL.
Un champ de cette variable record nous intéresse particulièrement, command_tag. Ce champ contient l’instruction qui vient d’être passée. Si on ne désire capter que l’évènement de création de table, il suffit de tester sa valeur qui contiendra dans ce cas… « CREATE TABLE ».
Un deuxième champ de cette variable nous intéresse aussi particulièrement, le nom de l’objet manipulé, donc ici de la table en cours de création. Ce champ se nomme object_identity. Il contient par ailleurs le nom du schéma de la table. Le champ est de la forme <schéma>.<table>.
Bien d’autres champs existent pouvant ramener de l’information quant à l’évènement capté.
Nous nous contenterons d’utiliser ici ces deux champs, command_tag et object_identity.
ddl_command_end
Le code qui suit montre l’utilisation de ce prédicat de déclenchement d’event trigger :
pg_event_trigger_ddl_commands, command_tag et object_identity
Ce tableau de records doit être parcouru pour accéder à ses valeurs. En l’occurrence, ici il n’y aura qu’une seule instruction avec ses caractéristiques qui se trouvera dans le tableau :
L’exemple ci-dessus parcoure le tableau des instructions DDL captées par l’event trigger et si l’instruction est « CREATE TABLE » alors le nom de la table est récupéré pour traitement ultérieur.
En résumé…
Nous disposons ici suffisamment d’éléments permettant de manipuler le « CREATE TABLE » que nous venons de capter.
Le dictionnaire de la base de données
Le dictionnaire d’une base de données est accessible au travers du schéma spécifique information_schema. Ce schéma contient diverses pseudo-tables permettant d’accéder au dictionnaire des objets propres à une base de données en particulier.
Existent dans ce dictionnaire notamment la liste des colonnes des tables, dans un pseudo-table nommée columns.
Il est de fait possible de récupérer les caractéristiques de toutes les colonnes d’une table en particulier.
Le curseur en exemple ci-dessous récupère les noms des colonnes pour traitement ultérieur :
SQL Dynamique
Si un concept en PLpg/SQL est aisé, c’est celui-ci.
L’exécution de SQL dynamique permet de construire des instructions SQL à la volée dans une fonction Plpg/SQL et de l’exécuter telle quelle. Le SQL dynamique permet de construire des instructions sans connaître à l’avance les éléments de l’instruction comme un nom de table, des noms de colonnes, des conditions.
L’exemple ci-dessous montre trois instructions SQL dynamique construites à la volée permettant de créer :
- une vue à partir de la table captée par l’event trigger,
- une fonction trigger (trigger de table/vue) prenant en charge la manipulation des colonnes de la vue,
- un trigger attaché à la vue exécutant la fonction trigger fraîchement créée
OID
L’oid d’un objet est l’identifiant unique de cet objet dans la base de données. L’oid peut notamment être l’identifiant unique d’une ligne dans une table, indépendamment de la clé primaire de la table.
Deux possibilités s’ouvrent pour intégrer l’oid dans une table :
- Intégrer sa création dans l’ordre de création de la table : CREATE TABLE … WITH OIDS
- Forcer toutes les tables d’une base de données à contenir l’oid lors de leur création, au moyen d’un paramètre de la base de données
Cette deuxième possibilité est obtenue en exécutant la commande suivante avec les droits administrateurs de la base de données :
Le code complet
Le code ci-dessous exécute les opérations suivantes :
- Création d’une fonction d’event trigger réalisant les opérations suivantes :
- Récupération du nom de la table en cours de création
- Récupération des colonnes de la table et stockage de morceaux d’instructions SQL dynamiques pour :
- Préparation l’alimentation des colonnes de la table avec « ####’ en tête de leur valeur initiale. Exemple : « ####Dupont » en lieu et place de « Dupont »
- Préparation l’instruction INSERT
- Préparation l’instruction UPDATE
- Renommage de la table initiale en « exemple_ »<nom de la table>, ce qui peut donner « exemple_personnes » pour la table initiale « personnes », à l’aide de SQL dynamique
- Création à l’aide du SQL dynamique :
- la fonction trigger de transformation des données pour les insertions et les mises à jour dans la table « personnes »
- le trigger attachant la fonction trigger à la table « exemple_personnes »
- la vue du nom de la table initiale. La vue s’appellerait dans notre cas « personnes »
- la fonction trigger de la vue pour la transmission des données vers la table « exemple_personnes »
- le trigger attachant la fonction trigger à la table
- Création de l’event trigger rattachant la fonction trigger à la base de données et donc son exécution par l’instance, puisqu’au bout du compte c’est bien l’instance qui exécute l’event trigger de la base de données
Exemple d’utilisation du code
Dans l’exemple ci-dessous, on crée la table « personnes », qui, nous le verront dans l’exemple est devenue « exemple_personnes » alors qu’une vue « personnes » a été créée en lieu et place de la table « personnes ».
Des instructions raise notice sont intégrées à la fonction event trigger afin de montrer la génération à la volée des vue, fonctions triggers, triggers.
Cet exemple montre ensuite les manipulations sur « personnes » avec leur effet sur « exemple_personnes » : insertions, sélections, mises à jour, suppression :