8.10. Tableaux

PostgreSQL autorise de définir des colonnes d'une table comme des tableaux multidimensionnels à longueur variable. Des tableaux de n'importe quel type, même défini par l'utilisateur, peuvent être créés. (Néanmoins, les tableaux de type composite ou de domaines ne sont pas encore supportés.)

8.10.1. Déclaration des types de tableaux

Pour illustrer l'utilisation des types de tableaux, créons cette table :

CREATE TABLE sal_emp (
    nom              text,
    paye_par_semaine integer[],
    planning         text[][]
);

Comme indiqué ci-dessus, un type de donnée tableau est nommé en ajoutant des crochets ([]) au type de donnée des éléments du tableau. La commande ci-dessus créera une table nommée sal_emp avec une colonne de type text (nom), un tableau à une dimension de type integer (paye_par_semaine), représentant le salaire d'un employé par semaine et un tableau à deux dimensions de type text (planning), représentant le planning hebdomadaire de l'employé.

La syntaxe pour CREATE TABLE permet de spécifier la taille exacte des tableaux, par exemple :

CREATE TABLE tictactoe (
    carres   integer[3][3]
);

Néanmoins, l'implémentation actuelle n'oblige pas au respect des limites en taille du tableau -- le comportement est identique à celui des tableaux dont la longueur n'a pas été spécifiée.

En fait, l'implémentation actuelle n'oblige pas non plus à déclarer le nombre de dimensions. Les tableaux d'un type d'élément particulier sont tous considérés comme étant du même type, sans vérification de la taille ou du nombre de dimensions. Donc, déclarer le nombre de dimensions ou la taille dans CREATE TABLE a uniquement un but de documentation, cela n'affecte pas le comportement lors de l'exécution.

Une syntaxe alternative, conforme au standard SQL:1999, pourrait être utilisée pour les tableaux à une dimension. paye_par_semaine pourrait avoir été définie ainsi :

    paye_par_semaine  integer ARRAY[4],

Cette syntaxe nécessite une constante de type entier pour indiquer la taille du tableau. Néanmoins, comme indiqué précédemment, PostgreSQL n'impose aucune restriction sur la taille.

8.10.2. Saisie de valeurs de type tableau

Pour écrire une valeur de type tableau comme une constante littérale, encadrez les valeurs des éléments par des accolades et séparez-les par des virgules. (Si vous connaissez le C, ce n'est pas différent de la syntaxe C pour initialiser les structures.) Vous pouvez mettre des guillemets doubles autour des valeurs des éléments, et devez le faire si elles contiennent des virgules ou des accolades. (Plus de détails ci-dessous.) Le format général d'une constante de type tableau est donc le suivant :

'{ val1 delim val2 delim ... }'

delim est le caractère de délimitation pour ce type, tel qu'il est enregistré dans son entrée pg_type. Parmi les types de données standards fournis par la distribution PostgreSQL, le type box utilise un point-virgule (;) mais tous les autres utilisent une virgule (,). Chaque val est soit une constante du type des éléments du tableau ou un sous-tableau. Voici un exemple d'une constante tableau

'{{1,2,3},{4,5,6},{7,8,9}}'

Cette constante a deux dimensions, un tableau 3 par 3 consistant en trois sous-tableaux d'entiers.

(Ces types de constantes de tableau sont en fait un cas particulier des constantes de type générique abordées dans la Section 4.1.2.5. La constante est traitée initialement comme une chaîne et passée à la routine de conversion d'entrées de tableau. Une spécification explicite du type pourrait être nécessaire.)

Maintenant, nous pouvons montrer quelques instructions INSERT.

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"rendez-vous", "repas"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"rencontre", "repas"}, {"rencontre"}}');
    ERROR:  multidimensional arrays must have array expressions with matching dimensions

Notez que les tableaux à plusieurs dimensions doivent avoir des limites correspondantes pour chaque dimension. Une différence provoque une erreur à l'exécution.

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"rencontre", "repas"}, {"entrainement", "présentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"petit-déjeuner", "consulting"}, {"rencontre", "repas"}}');

Une limitation de l'implémentation actuelle des tableaux existe, les éléments individuels d'un tableau ne peuvent avoir la valeur SQL NULL. Le tableau entier peut être NULL mais vous ne pouvez pas avoir des éléments NULL dans un tableau avec d'autres éléments non NULL. (Ceci est susceptible de changer dans le futur.)

Le résultat des deux insertions précédentes ressemble à ceci :

SELECT * FROM sal_emp;
 nom   |      paye_par_semaine     |      planning
-------+---------------------------+--------------------
Bill  | {10000,10000,10000,10000} | {{rencontre,repas},{entrainement,présentation}}
Carol | {20000,25000,25000,25000} | {{petit-déjeuner,consulting},{rencontre,repas}}
(2 rows)

La syntaxe du constructeur ARRAY peut aussi être utilisée :

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['rendez-vous', 'repas'], ['entrainement','présentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['petit-déjeuner', 'consulting'], ['rencontre', 'repas']]);

Notez que les éléments du tableau sont des constantes SQL ordinaires ou des expressions ; par exemple, les chaînes de caractères littérales sont encadrées par des guillemets simples au lieu de guillemets doubles comme cela serait le cas dans un tableau littéral. La syntaxe du constructeur ARRAY est discutée plus en profondeur dans la Section 4.2.10.

8.10.3. Accès aux tableaux

Maintenant, nous pouvons lancer quelques requêtes sur la table. Tout d'abord, montrons comment accéder à un seul élément du tableau à la fois. Cette requête retrouve le nom des employés dont la paye a changé la deuxième semaine :

SELECT nom FROM sal_emp WHERE paye_par_semaine[1] <> paye_par_semaine[2];

 nom
-------
 Carol
(1 row)

Les nombres du tableau sont écrit entre crochets. Par défaut, PostgreSQL utilise la convention des nombres commençant à 1 pour les tableaux, c'est-à-dire un tableau à n éléments commence avec array[1] et finit avec array[n].

Cette requête récupère la paye de la troisième semaine pour tous les employés :

SELECT paye_par_semaine[3] FROM sal_emp;

 paye_par_semaine
------------------
          10000
          25000
(2 rows)

Nous pouvons aussi accéder à des parties rectangulaires arbitraires ou à des sous-tableaux. Une partie d'un tableau est notée par l'écriture extrémité basse:extrémité haute quelle que soit la dimension des tableaux. Par exemple, cette requête retrouve le premier élément du planning de Bill pour les deux premiers jours de la semaine :

SELECT planning[1:2][1:1] FROM sal_emp WHERE nom = 'Bill';

      planning
--------------------
 {{rendez-vous},{entrainement}}
(1 row)

Nous aurions aussi pu écrire

SELECT planning[1:2][1] FROM sal_emp WHERE nom = 'Bill';

en ayant le même résultat. Une opération d'indiçage de rangée est toujours prise pour représenter une tranche de rangée si un indice quelconque est écrit sous la forme inférieur:supérieur. Une limite basse de 1 est supposée pour toute tranche dont seule une valeur est spécifiée. Voici un autre exemple :

SELECT planning[1:2][2] FROM sal_emp WHERE nom = 'Bill';

         planning
---------------------------
 {{rendez-vous,repas},{entrainement,présentation}}
(1 row)

Récupérer en dehors des limites actuelles d'un tableau amène une valeur SQL NULL, pas une erreur. Par exemple, si planning a les dimensions [1:3][1:2], alors référencer planning[3][3] a un résultat NULL. De la même façon, une référence sur un tableau avec le mauvais nombre d'indices amène une valeur NULL plutôt qu'une erreur. Récupérer une partie d'un tableau complètement en dehors des limites actuelles renvoie un tableau NULL ; mais si la partie demandée est partiellement intégrée aux limites du tableau, alors il est silencieusement réduit à la région d'intersection.

Les dimensions actuelles de toute valeur d'un tableau sont disponibles avec la fonction array_dims :

SELECT array_dims(planning) FROM sal_emp WHERE nom = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

array_dims donne un résultat de type text, ce qui est pratique à lire mais peut-être moins simple à interpréter pour les programmes. Les dimensions sont aussi récupérables avec array_upper et array_lower, qui renvoient respectivement la limite haute et basse d'un tableau spécifié.

SELECT array_upper(planning, 1) FROM sal_emp WHERE nom = 'Carol';

 array_upper
-------------
           2
(1 row)

8.10.4. Modification de tableaux

La valeur d'un tableau peut être complètement remplacée :

UPDATE sal_emp SET paye_par_semaine = '{25000,25000,27000,27000}'
    WHERE nom = 'Carol';

ou en utilisant la syntaxe de l'expression ARRAY :

UPDATE sal_emp SET paye_par_semaine = ARRAY[25000,25000,27000,27000]
    WHERE nom = 'Carol';

On peut aussi mettre à jour un seul élément d'un tableau :

UPDATE sal_emp SET paye_par_semaine[4] = 15000
    WHERE nom = 'Bill';

ou faire une mise à jour par tranche :

UPDATE sal_emp SET paye_par_semaine[1:2] = '{27000,27000}'
    WHERE nom = 'Carol';

Une valeur de tableau enregistrée peut être agrandie pour affecter un élément adjacent à ceux déjà présents ou en affectant à une partie adjacente une partie des données déjà présentes. Par exemple, si le tableau mon_tableau a pour le moment quatre éléments, il en aura cinq après une mise à jour qui a affecté mon_tableau[5]. Actuellement, l'agrandissement de cette façon est seulement autorisé pour les tableaux à une dimension, et non pas pour les tableaux multidimensionnels.

L'affectation de parties d'un tableau permet la création de tableaux dont l'indice de départ n'est pas 1. Par exemple, vous pourriez affecter mon_tableau[-2:7] pour créer un tableau avec les valeurs d'indices allant de -2 à 7.

Les valeurs de nouveaux tableaux peuvent aussi être construites en utilisant l'opérateur de concaténation, ||.

SELECT ARRAY[1,2] || ARRAY[3,4];
   ?column?
---------------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

L'opérateur de concaténation autorise un élément à être placé au début ou à la fin d'un tableau à une dimension. Il accepte aussi deux tableaux à N dimensions, ou un tableau à N dimensions et un à N+1 dimensions.

Lorsqu'un élément seul est placé au début d'un tableau à une dimension, le résultat est un tableau disposant d'une limite inférieure égale à la limite inférieure de l'opérande du côté droit moins un. Lorsqu'un élément est placé à la fin d'un tableau à une dimension, le résultat est un tableau contenant la limite inférieure de l'opérande gauche. Par exemple :

SELECT array_dims(1 || ARRAY[2,3]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

Lorsque deux tableaux ayant un même nombre de dimensions sont concaténés, le résultat conserve la limite inférieure de l'opérande gauche. Le résultat est un tableau comprenant chaque élément de l'opérande gauche suivi de chaque élément de l'opérande droit. Par exemple :

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

Lorsqu'un tableau à N dimensions est placé au début ou à la fin d'un tableau à N+1 dimensions, le résultat est analogue au cas ci-dessus. Chaque sous-tableau de dimension N est en quelque sorte un élément de la dimension externe d'un tableau à N+1 dimensions. Par exemple :

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [0:2][1:2]
(1 row)

Un tableau peut aussi être construit en utilisant les fonctions array_prepend, array_append ou array_cat. Les deux premières supportent seulement les tableaux à une dimension alors que array_cat supporte les tableaux multidimensionnels. Notez que l'opérateur de concaténation vu ci-dessus est préféré à l'utilisation directe de ces fonctions. En fait, les fonctions sont utilisées principalement pour l'implémentation de l'opérateur de concaténation. Néanmoins, elles pourraient être directement utiles dans la création d'agrégats définis par l'utilisateur. Quelques exemples :

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
   array_cat
---------------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

8.10.5. Recherche dans des tableaux

Pour rechercher une valeur dans un tableau, vous devez vérifier chaque valeur dans le tableau. Ceci peut se faire à la main si vous connaissez la taille du tableau. Par exemple :

SELECT * FROM sal_emp WHERE paye_par_semaine[1] = 10000 OR
                            paye_par_semaine[2] = 10000 OR
                            paye_par_semaine[3] = 10000 OR
                            paye_par_semaine[4] = 10000;

Néanmoins, ceci devient rapidement fastidieux pour les gros tableaux et n'est pas très utile si la taille du tableau n'est pas connue. Une autre méthode est décrite dans Section 9.17. La requête ci-dessus est remplaçable par :

SELECT * FROM sal_emp WHERE 10000 = ANY (paye_par_semaine);

De plus, vous pouvez trouvez les lignes où le tableau n'a que des valeurs égales à 10000 avec :

SELECT * FROM sal_emp WHERE 10000 = ALL (paye_par_semaine);

Astuce : Les tableaux ne sont pas toujours initialisés ; rechercher des éléments spécifiques d'un tableau pourrait être un signe d'une mauvaise conception de la base de données. Utilisez plutôt une table séparée avec une ligne pour chaque élément faisant parti du tableau. Cela sera plus simple pour une recherche et fonctionnera mieux dans le cas d'un grand nombre d'éléments.

8.10.6. Syntaxe d'entrée et de sortie des tableaux

La représentation externe du type texte d'une valeur d'un tableau consiste en des éléments interprétés suivant les règles de conversion d'entrées/sorties pour le type de l'élément du tableau, plus des décorations indiquant la structure du tableau. L'affichage consiste en des accolades ({ et }) autour des valeurs du tableau et des caractères de délimitation entre éléments adjacents. Le caractère délimiteur est habituellement une virgule (,) mais peut être autre chose : il est déterminé par le paramètre typdelim du type de l'élément tableau (parmi les types de données standards supportés par l'implémentation de PostgreSQL, le type box utilise un point-virgule (;) mais tous les autres utilisent la virgule). Dans un tableau multidimensionnel, chaque dimension (row, plane, cube, etc.) utilise son propre niveau d'accolades et les délimiteurs doivent être utilisés entre des entités adjacentes au sein d'accolades de même niveau.

La routine de sortie du tableau placera des guillemets doubles autour des valeurs des éléments si elles sont des chaînes vides ou contiennent des accolades, des caractères délimiteurs, des guillemets doubles, des antislash ou des espaces. Les guillemets doubles et les antislash intégrés aux valeurs des éléments seront échappés avec un antislash. Pour les types de données numériques, on peut supposer sans risque que les doubles guillemets n'apparaîtront jamais, mais pour les types de données texte, vous devez vous préparer à gérer la présence et l'absence de guillemets. (Ceci est un changement du comportement à partir de la version pré-7.2 de PostgreSQL.)

Par défaut, la valeur de la limite basse d'un tableau est initialisée à 1. Si une des dimensions du tableau a une limite basse différente de 1, un affichage supplémentaire indiquant les dimensions réelles du tableau précède l'affichage de la structure du tableau. Cet affichage consiste en des crochets ([]) autour de chaque limite basse et haute d'une dimension avec un délimiteur deux-points (:) entre chaque. L'affichage des dimensions du tableau est suivie par un signe d'égalité (=). Par exemple :

SELECT 1 || ARRAY[2,3] AS array;

    array
---------------
[0:2]={1,2,3}
(1 row)

SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;

          array
--------------------------
[0:1][1:2]={{1,2},{3,4}}
(1 row)

Cette syntaxe peut aussi être utilisée pour spécifier des indices de tableau différents des indices par défaut. Par exemple :

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

Comme indiqué précédemment, lors de l'écriture d'une valeur de tableau, vous pourriez écrire des guillemets doubles autour de chaque élément individuel de tableau. Vous devez le faire si leur absence autour d'un élément induit en erreur l'analyseur de la valeur du tableau. Par exemple, les éléments contenant des crochets, virgules (ou un caractère délimiteur), guillemets doubles, antislashs ou espace (en début comme en fin) doivent avoir des guillemets doubles. Pour placer un guillemet double ou un antislash dans une valeur d'élément d'un tableau, faites-le précéder d'un antislash. Autrement, vous pouvez échapper tous les caractères de données qui sont utilisés dans la syntaxe du tableau.

Vous pouvez ajouter des espaces avant un crochet gauche ou après un crochet droit. Vous pouvez aussi ajouter des espaces avant tout élément individuel. Dans tous les cas, les espaces seront ignorés. Par contre, les espaces à l'intérieur des éléments entre guillemets doubles ou entourés par des caractères autres que des espaces ne sont pas ignorés.

Note : Rappelez-vous que ce que vous écrivez comme commande SQL sera tout d'abord interprété en tant que chaîne littérale puis en tant que tableau. Ceci double le nombre d'antislash dont vous aurez besoin. Par exemple, pour insérer une valeur de tableau de type text contenant un antislash et un guillemet double, vous aurez besoin d'écrire

INSERT ... VALUES ('{"\\\\","\\""}');

Le processeur de la chaîne littérale supprime un niveau d'antislash, donc ce qui arrive à l'analyseur de tableau ressemble à {"\\","\""}. À la place, les chaînes remplissant l'entrée du type de données text deviennent respectivement \ et ". (Si nous travaillions avec un type de données dont la routine d'entrée traitait aussi les antislash de manière spéciale, bytea par exemple, nous pourrions avoir besoin d'au plus huit antislash dans la commande pour en obtenir un dans l'élément stocké.) Les guillemets dollar (voir Section 4.1.2.2) pourraient être utilisés pour éviter le besoin des doubles antislashs.

Astuce : La syntaxe du constructeur ARRAY (voir Section 4.2.10) est souvent plus facile à utiliser que la syntaxe du tableau littéral lors de l'écriture de valeurs du tableau en commandes SQL. Avec ARRAY, les valeurs de l'élément individuel sont écrites de la même façon qu'elles auraient été écrites si elles n'avaient pas fait partie d'un tableau.