8.10. Tableaux

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ées 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 SQL99, 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, en fait, vous devez le faire si elles contiennent des virgules ou des accolades. (Plus de détails ci-dessous.) Donc, le format général d'une constante de type tableau est la suivante :

'{ 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. (Pour tous les types intégrés, il s'agit du caractère virgule << , >>.) Chaque val est soit une constante 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 actuellement le seul cas spécifique de constantes de type générique discutés dans la Section 4.1.2.4. 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}',
    '{{"conférence", "consultation"}, {"rencontre"}}');

Une limitation de l'implémentation actuelle des tableaux est que 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 peut amener des résultats surprenants. Par exemple, 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},{""}}
 Carol | {20000,25000,25000,25000} | {{conférence},{rencontre}}
(2 rows)

Parce que l'élément [2][2] de planning est manquant dans chaque instruction INSERT, l'élément [1][2] est annulé.

La syntaxe de l'expression ARRAY pourrait aussi être utilisée :

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

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['conférence', 'consultation'], ['rencontre', '']]);
SELECT * FROM sal_emp;
 nom   |      paye_par_semaine     |           planning
-------+---------------------------+-------------------------------
 Bill  | {10000,10000,10000,10000} | {{rendez-vous,repas},{"",""}}
 Carol | {20000,25000,25000,25000} | {{conférence,consultation},{rencontre,""}}
(2 rows)

Notez qu'avec cette syntaxe, les tableaux multidimensionels doivent correspondre parfaitement pour chaque dimension. Sans correspondance, un rapport d'erreur apparaît, plutôt que d'oublier silencieusement les valeurs comme dans le cas précédent. Par exemple :

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['conférence', 'consultation'], ['rencontre']]);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

Notez aussi 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 d'expression 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 retrouve 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 quelque 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},{""}}
(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},{"",""}}
(1 row)

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:1]
(1 row)

array_dims donne un résultat de type text, ce qui est agréable à 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';

Un tableau peut aussi être mis à jour pour un seul élément :

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

ou 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 montableau a actuellement quatre éléments, il en aura cinq après une mise à jour qui a affecté montableau[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 n'utilisant pas d'indice inférieur. Par exemple, vous pourriez affecter montableau[-2:7] pour créer un tableau avec les valeurs d'indices inférieurs 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 seul élément 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 premiers 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 long pour les gros tableaux et n'est pas très utile si la taille du tableau n'est pas certaine. 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);

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. La décoration 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 la configuration typdelim pour le type de l'élément du 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. Vous pouvez insérer un espace blanc avant une accolade gauche, après une accolade droite ou avant toute chaîne d'un élément individuel. Un espace blanc après un élément n'est pas ignoré, néanmoins : après avoir passé l'espace blanc du début, tout - jusqu'à la prochaine accolade droite ou délimiteur - est interprété comme valeur de l'élément.

Comme indiqué précédemment, lors de l'écriture de la valeur d'un tableau, vous pouvez écrire des doubles guillemets autour de chaque élément individuel du tableau. Vous devez le faire si la valeur de l'élément risque de gêner l'analyseur. Par exemple, les éléments contenant des accolades, des virgules (ou quelque soit le caractère délimiteur), des guillemets doubles, des anti-slash ou des espaces blancs en début doivent être entre guillemets doubles. Pour mettre un guillemet double ou un anti-slash dans la valeur d'un élément d'un tableau entre guillemets, précédez-le avec un anti-slash. Autrement, vous pouvez utiliser le caractère d'échappement anti-slash pour protéger tous les caractères de données qui pourraient être pris pour un élément de syntaxe de tableau ou des espaces blancs à ignorer.

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 anti-slash ou des espaces blancs. Les guillemets doubles et les anti-slash intégrés aux valeurs des éléments auront un caractère d'échappement, l'anti-slash. Pour les types de données numériques, il est sain de supposer 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.)