Pl/PgSQL: Parcourir un tableau

Pour PostgreSQL >= 9.1

Depuis PostgreSQL 9.1, nous pouvons utiliser l’opérateur FOREACH, comme ceci:

Pour PostgreSQL > 8.3 et <= 9.0

Pour parcourir un tableau de données, ça pourrait être très simple en utilisant FOR ... IN ... mais cette synthaxe utilise uniquement des données de type RECORD pour fonctionner. C’est pourquoi, depuis PostgreSQL 8.4, il y a une fonction unnest qui permet de transformer un tableau (array) en un RECORD. Avant, nous allons créer cette fonction.

Dans votre bloc Pl/PgSQL, faites comme ceci:

Données:

  • v_monarray est un tableau de données (exemple: integer[] )
  • v_array_data est une variable contenant la valeur de la donnée du tableau du tour (exemple: integer)
    Note: Doit être du même type que la donnée du tableau

Pour PostgreSQL <= 8.3

Pour ces versions, nous allons créer la fonction unnset (voir article sur le wiki de PostgreSQL) comme ceci:

Ainsi, vous pouvez appliquer l’exemple pour PostgreSQL 8.4.

Sinon, vous pouvez aussi utiliser WHILE comme ceci:

Avec:

  • v_array_count la taille du tableau
  • v_loop_i une variable de type integer initialisée à 1
facebooktwittergoogle_plusredditpinterestlinkedinmail

Pl/PgSQL: Noms de variables dynamiques

Si dans un trigger vous voulez récupérer un champ qui change à chaque fois (nom dynamique), ou plus généralement si vous voulez récupérer la valeur d’un champ xxxxxx d’un RECORD, il faut utiliser EXECUTE. Soient:

  • NEW, la nouvelle ligne (automatiquement créé avec les triggers sur INSERT et UPDATE)
    Note: C’est une variable de type RECORD. Ainsi, vous pouvez la remplacer par n’importe quel autre RECORD.
  • v_critere, le nom du champ à récupérer
  • v_value, la variable dans laquelle on veut mettre la valeur
  • monschema.matable, le nom de la table qui correspond au RECORD.

Ainsi, si v_critere = id_somethink, alors la valeur du champ “id_somethink” du RECORD sera placée dans la variable v_value. “Tout simplement” :-)

Merci beaucoup à Dimitri et Guillaume de la liste PostgreSQL pour m’avoir aider à trouver cette solution qui n’est pas implantée malgré le patch proposé.

facebooktwittergoogle_plusredditpinterestlinkedinmail

Triggers et clés étrangères

Sous PostgreSQL, il y a plusieurs langages de procédures comme plpgsql pour éxécuter des commandes (surtout SQL) au sein de la base ou pgplsh pour créer des fonctions écrites en shell.

Avec ces langages, on peut écrire des fonctions qui seront appellées via des SELECT ou PERFORM dans le cas d’une éxécution depuis une autre fonction. Cependant, elles ont un autre avantages : elle peuvent être appellées par des triggers. Ce sont, en français, des déclencheurs, c’est-à-dire que c’est fonctions sont appelée avant ou après chaque ajout, modification ou suppression de ligne ou bien avant ou après chaque instruction (qui peuvent contenir plusieurs opérations de modification de ligne).

Les triggers peuvent modifier les données dans le cas d’une modification ou d’une insertion mais peuvent aussi empècher l’éxécution de l’opération dans tous les cas (à condition que le trigger soit paramétré pour être éxécuté avant l’opération sur la ligne). Les triggers après-insertion sont plus utiles qu’ils peuvent en avoir l’air car il permettent de répliquer des informations par exemple, ou de complèter des tables de backups par exemple. De plus, ces triggers AFTER ont connaissance des informations de séquences (clés primaires auto-complètées par exemple). Continue reading Triggers et clés étrangères

facebooktwittergoogle_plusredditpinterestlinkedinmail