Apr 19, 2024 by Robert Gravelle

Les règles PostgreSQL offrent un mécanisme puissant pour contrôler l'exécution des requêtes et appliquer la manipulation des données au sein de la base de données. Comprendre la syntaxe et l'utilisation des règles est essentiel pour exploiter efficacement leurs capacités. Dans l'article de la semaine dernière, nous avons exploré le fonctionnement des règles PostgreSQL et en quoi elles diffèrent des déclencheurs. Le suivi d'aujourd'hui couvrira leur syntaxe en détail avec des exemples plus pratiques utilisant la base de données exemple gratuite "dvdrental" sample database.

Anatomie des règles PostgreSQL

Les règles PostgreSQL se composent de plusieurs composants clés qui définissent leur comportement :

• Instruction CREATE RULE : Pour créer une règle, nous utilisons l'instruction CREATE RULE suivie d'un nom de règle et de la définition de la règle.
• Événement de règle : les règles peuvent être déclenchées par divers événements, notamment SELECT , INSERT , UPDATE , DELETE ou une combinaison (ALL).
• Action de règle : l'action spécifie ce qui doit se produire lorsque la règle est déclenchée. Il peut s'agir d'une instruction SQL telle que SELECT, INSERT, UPDATE, DELETEcode> ou d'une action personnalisée.
• Condition de règle : les conditions sont facultatives et permettent de déclencher des règles uniquement lorsque certains critères sont remplis. Ils sont spécifiés à l'aide d'une clause WHERE.

Exemples pratiques utilisant la base de données exemple « dvdrental »

Exemple 1 : insertions d'audit

Supposons que nous souhaitions enregistrer toutes les insertions dans la table « customer» à des fins d'audit. Nous aurons d’abord besoin d’une table pour stocker les données d’audit :

CREATE TABLE customer_audit (
    action_type VARCHAR(10),
    customer_id INT,
    audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Nous pouvons également créer la table ci-dessus à l'aide de Navicat for PostgreSQL 16 . Voici à quoi cela ressemble :

Nous allons maintenant créer une règle qui insère un enregistrement dans une table d'audit chaque fois qu'un nouveau client est ajouté :

CREATE RULE log_customer_insert AS
    ON INSERT TO customer
    DO ALSO
        INSERT INTO customer_audit (action_type, customer_id)
        VALUES ('INSERT', NEW.customer_id);

Dans Navicat, vous trouverez les règles d'une table donnée dans l'onglet "Règles" du Table Designer. Voici la règle log_customer_insert :

Exemple 2 : Restriction des mises à jour

Disons que nous souhaitons empêcher la mise à jour de la date de retour de location une fois qu'elle a été fixée. Nous pouvons créer une règle qui bloque toute tentative de mise à jour de la colonne de date de retour une fois qu'elle a été initialement définie :

CREATE RULE prevent_return_date_update AS
    ON UPDATE TO rental
    WHERE OLD.return_date IS NOT NULL AND NEW.return_date IS DISTINCT FROM OLD.return_date
    DO INSTEAD NOTHING;

Voici la règle Prevent_return_date_update dans Navicat :

Vous reconnaîtrez peut-être la règleforce_min_rental_duration de l'article de la semaine dernière.

Example 3: Transformation de données

Supposons que nous souhaitions transformer le format des numéros de téléphone stockés dans la table « adresse » du format international au format local. Nous pouvons créer une règle qui met automatiquement à jour les numéros de téléphone chaque fois qu'une nouvelle adresse est insérée :

CREATE RULE transform_phone_number AS
    ON INSERT TO address
    DO ALSO
        UPDATE address
        SET phone = '+1-' || SUBSTRING(phone FROM 3)
        WHERE address_id = NEW.address_id;

Vous avez besoin de plus d'espace pour saisir l'instruction WHERE ou Definition statement ? En cliquant sur le bouton points de suspension [...] à côté de la zone de texte, vous ouvrez une grande zone de texte dans laquelle vous pouvez afficher et rédiger la déclaration complète. Voici la règle transform_phone_number dans Navicat qui affiche l'intégralité

Conclusion

Les règles PostgreSQL offrent un ensemble d'outils polyvalents pour implémenter une logique complexe et renforcer l'intégrité des données dans la base de données. En explorant divers exemples tels que l'audit des insertions, la restriction des mises à jour et la transformation des données, les développeurs peuvent mieux comprendre comment les règles peuvent être appliquées pour répondre efficacement à diverses exigences. Grâce au système de règles flexible de PostgreSQL, les développeurs peuvent adapter le comportement des bases de données pour répondre aux besoins spécifiques de l'entreprise tout en garantissant la cohérence et la fiabilité des données.