8.10. 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 de la 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 autre syntaxe, conforme au standard SQL, 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.

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 ... }'

où 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 soit 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, « Constantes d'autres types ». 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"}, {"rendez-vous"}}');
    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}',
    '{{"rendez-vous", "repas"}, {"entrainement", "présentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"petit-déjeuner", "consultation"}, {"rendez-vous", "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} | {{rendez-vous,repas},{entrainement,présentation}}
Carol  | {20000,25000,25000,25000} | {{petit-déjeuner,consultation},{rendez-vous,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', 'consultation'], ['rendez-vous', '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, « Constructeurs de 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 indices du tableau sont écrit entre crochets. Par défaut, PostgreSQL™ utilise la convention des indices 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)

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}}

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 la Section 9.17, « Comparaisons de lignes et de tableaux ». La requête ci-dessus est remplaçable par :

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

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

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

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 antislashs ou des espaces. Les guillemets doubles et les antislashs 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 datant 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.