8.15. Tableaux

La création de la table suivante permet d'illustrer l'utilisation des types tableaux :

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

Comme indiqué ci-dessus, un type de données tableau est nommé en ajoutant des crochets ([]) au type de données des éléments du tableau. La commande ci-dessus crée 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 de CREATE TABLE permet de préciser la taille exacte des tableaux, par exemple :

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

Néanmoins, l'implantation actuelle ignore toute limite fournie pour la taille du tableau, c'est-à-dire que le comportement est identique à celui des tableaux dont la longueur n'est pas précisée.

De plus, l'implantation 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, quels que soient leur taille ou le nombre de dimensions. Déclarer la taille du tableau ou le nombre de dimensions dans CREATE TABLE n'a qu'un but documentaire. Le comportement de l'application n'en est pas affecté.

Une autre syntaxe, conforme au standard SQL via l'utilisation du mot clé ARRAY, peut être employée pour les tableaux à une dimension. paye_par_semaine peut être défini ainsi :

paye_par_semaine  integer ARRAY[4],

ou si aucune taille du tableau n'est spécifiée :

    paye_par_semaine  integer ARRAY,
   

Néanmoins, comme indiqué précédemment, PostgreSQL™ n'impose aucune restriction sur la taille dans tous les cas.

Pour écrire une valeur de type tableau comme une constante littérale, on encadre les valeurs des éléments par des accolades et on les sépare par des virgules (ce n'est pas différent de la syntaxe C utilisée pour initialiser les structures). Des guillemets doubles peuvent être positionnés autour des valeurs des éléments. C'est d'ailleurs obligatoire 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 standard fournis par la distribution PostgreSQL™, tous utilisent une virgule (,), sauf pour le type box qui utilise un point-virgule (;). Chaque val est soit une constante du type des éléments du tableau soit un sous-tableau.

Exemple de 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.

Pour initialiser un élément d'un tableau à NULL, on écrit NULL pour la valeur de cet élément. (Toute variante majuscule et/ou minuscule de NULL est acceptée.) Si « NULL » doit être utilisé comme valeur de chaîne, on place des guillemets doubles autour.

Ces types de constantes tableau sont en fait un cas particulier des constantes de type générique abordées dans la Section 4.1.2.7, « 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 peut être nécessaire.

Quelques instructions INSERT :

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"}}');

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

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)

Les tableaux multi-dimensionnels doivent avoir des échelles correspondantes pour chaque dimension. Une différence cause la levée d'une erreur. Par exemple :

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
   

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']]);

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 est le cas dans un tableau littéral. La syntaxe du constructeur ARRAY est discutée plus en profondeur dans la Section 4.2.12, « Constructeurs de tableaux ».

Quelques requêtes lancées sur la table permettent d'éclairer le propos précédent. Tout d'abord, l'accès à un seul élément du tableau. Cette requête retrouve le nom des employés dont la paye a changé au cours de 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 écrits 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].

Récupérer la paye de la troisième semaine de tous les employés :

SELECT paye_par_semaine[3] FROM sal_emp;

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

Il est également possible d'accéder à des parties rectangulaires arbitraires ou à des sous-tableaux. Une partie de tableau est indiquée par l'écriture extrémité basse:extrémité haute sur n'importe quelle dimension. Ainsi, la requête suivante retourne 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)

Si l'une des dimensions est écrite comme une partie, c'est-à-dire si elle contient le caractère deux-points, alors toutes les dimensions sont traitées comme des parties. Toute dimension qui n'a qu'un numéro (pas de deux-points), est traitée comme allant de 1 au nombre indiqué. Par exemple, [2] est traitée comme [1:2], comme le montre cet exemple :

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

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

Pour éviter la confusion avec le cas sans indice, il est meilleur d'utiliser la syntaxe avec indice pour toutes les dimensions, c'est-à-dire [1:2][1:1] et non pas [2][1:1].

Il est possible d'omettre la limite basse et/ou la limite haute dans les indices. La limite manquante est remplacée par la limite basse ou haute des dimensions du tableau. Par exemple :

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

        planning
------------------------
 {{lunch},{presentation}}
(1 row)

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

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Une expression indicée de tableau retourne NULL si le tableau ou une des expressions est NULL. De plus, NULL est renvoyé si un indice se trouve en dehors de la plage du tableau (ce cas n'amène pas d'erreur). Par exemple, si planning a les dimensions [1:3][1:2], faire référence à planning[3][3] donne un résultat NULL. De la même façon, une référence sur un tableau avec une valeur d'indices incorrecte retourne une valeur NULL plutôt qu'une erreur.

Une expression de découpage d'un tableau est aussi NULL si, soit le tableau, soit une des expressions indicées est NULL. Néanmoins, dans certains cas particuliers comme la sélection d'une partie d'un tableau complètement en dehors de la plage de ce dernier, l'expression de cette partie est un tableau vide (zéro dimension) et non pas un tableau NULL. (Ceci ne correspond pas au comportement sans indice, et est fait pour des raisons historiques.) Si la partie demandée surcharge partiellement les limites du tableau, alors elle est réduite silencieusement à la partie surchargée au lieu de renvoyer NULL.

Les dimensions actuelles de toute valeur de type 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 s'avérer plus difficile à interpréter par les programmes. Les dimensions sont aussi récupérables avec array_upper et array_lower, qui renvoient respectivement la limite haute et la limite basse du tableau précisé :

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

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

array_length renverra la longueur de la dimension indiquée pour le tableau :

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

 array_length
--------------
            2
(1 row)
   

cardinality renvoie le nombre total d'éléments d'un tableau sur toutes ses dimensions. Autrement dit, c'est le nombre de lignes que renverrait un appel à la fonction unnest :

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

 cardinality
-------------
           4
(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';

Les syntaxes des indices avec la limite basse et/ou la limite upper-bound omise peuvent aussi être utilisées lors de la mise à jour d'une valeur d'un tableau qui est différent de NULL ou à plus de zéro dimension (sinon, il n'existe pas de limite à substituer).

Un tableau peut être agrandi en y stockant des éléments qui n'y sont pas déjà présents. Toute position entre ceux déjà présents et les nouveaux éléments est remplie avec la valeur NULL. Par exemple, si le tableau mon_tableau a actuellement quatre éléments, il en aura six après une mise à jour qui affecte mon_tableau[6] car mon_tableau[5] est alors rempli avec une valeur NULL. Actuellement, l'agrandissement de cette façon n'est autorisé que pour les tableaux à une dimension, pas pour les tableaux multidimensionnels.

L'affectation par parties d'un tableau permet la création de tableaux dont l'indice de départ n'est pas 1. On peut ainsi affecter, par exemple, mon_tableau[-2:7] pour créer un tableau avec des 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.

Quand un élément seul est poussé soit au début soit à la fin d'un tableau à une dimension, le résultat est un tableau avec le même indice bas que l'opérande du tableau. Par exemple :

        SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 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
------------
 [1:3][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 ne supportent que les tableaux à une dimension alors que array_cat supporte les tableaux multidimensionnels. 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}}

Dans les cas simples, l'opération de concaténation discutée ci-dessus est préférée à l'utilisation directe de ces fonctions. Néanmoins, comme l'opérateur de concaténation est surchargé pour servir les trois cas, certaines utilisations peuvent bénéficier de l'utilisation d'une fonction pour éviter toute ambiguité. Par exemple :

SELECT ARRAY[1, 2] || '{3, 4}';  -- le litéral non typé est pris pour un tableau
 ?column?
-----------
 {1,2,3,4}

SELECT ARRAY[1, 2] || '7';                 -- idem pour celui-ci
ERROR:  malformed array literal: "7"

SELECT ARRAY[1, 2] || NULL;                -- pareil pour un NULL
 ?column?
----------
 {1,2}
(1 row)

SELECT array_append(ARRAY[1, 2], NULL);    -- ceci peut être voulu
 array_append
--------------
 {1,2,NULL}
   

Dans l'exemple ci-dessus, l'analyseur voit un tableau d'entiers d'un côté de l'opérateur de concaténation et une constante de type indéterminé de l'autre. L'heuristique utilisé pour résoudre le type de la constante revient à assumer qu'elle est de même type que l'autre entrée de l'opérateur -- dans ce cas, un tableau d'entiers. Donc l'opérateur de concaténation est supposé représenter array_cat, et non pas array_append. Quand le choix est erroné, cela peut se corriger en convertissant la constante dans le type de données d'un élément du tableau. L'utilisation de la fonction array_append peut être préférable.

Pour rechercher une valeur dans un tableau, il faut vérifier chaque valeur dans le tableau. Ceci peut se faire à la main lorque la taille du tableau est connue. 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;

Ceci devient toutefois 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.23, « 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 la même façon, on trouve les lignes où le tableau n'a que des valeurs égales à 10000 avec :

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

Sinon, la fonction generate_subscripts peut être utilisée. Par exemple :

SELECT * FROM
   (SELECT paye_par_semaine,
           generate_subscripts(paye_par_semaine, 1) AS s
      FROM sal_emp) AS foo
 WHERE paye_par_semaine[s] = 10000;
   

Cette fonction est décrite dans Tableau 9.59, « Fonctions de génération d'indices ».

Vous pouvez aussi chercher dans un tableau en utilisant l'opérateur &&, qui vérifie si l'opérande gauche a des éléments communs avec l'opérande droit. Par exemple :

SELECT * FROM sal_emp WHERE paye_par_semaine && ARRAY[10000];
   

Les opérateurs sur les tableaux sont décrits plus en profondeur dans Section 9.18, « Fonctions et opérateurs de tableaux ». Leur performances peuvent profiter d'un index approprié, comme décrit dans Section 11.2, « Types d'index ».

Vous pouvez aussi rechercher des valeurs spécifiques dans un tableau en utilisant les fonctions array_position et array_positions. La première renvoie l'indice de la première occurence d'une valeur dans un tableau. La seconde renvoie un tableau avec les indices de toutes les occurences de la valeur dans le tableau. Par exemple :

        SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 array_positions
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}

La représentation externe du type texte d'une valeur de type 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 est constitué d'accolades ({ et }) autour des valeurs du tableau et de caractères de délimitation entre éléments adjacents. Le caractère délimiteur est habituellement une virgule (,) mais peut différer : il est déterminé par le paramètre typdelim du type de l'élément tableau. Parmi les types de données standard supportés par l'implantation de PostgreSQL™, seul le type box utilise un point-virgule (;), tous les autres utilisant 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 place des guillemets doubles autour des valeurs des éléments si ce sont des chaînes vides, si elles contiennent des accolades, des caractères délimiteurs, des guillemets doubles, des antislashs ou des espaces ou si elles correspondent à NULL. Les guillemets doubles et les antislashs intégrés aux valeurs des éléments sont échappés à l'aide d'un antislash. Pour les types de données numériques, on peut supposer sans risque que les doubles guillemets n'apparaissent jamais, mais pour les types de données texte, il faut être préparé à gérer la présence et l'absence de guillemets.

Par défaut, la valeur de la limite basse d'un tableau est initialisée à 1. Pour représenter des tableaux avec des limites basses différentes, les indices du tableau doivent être indiqués explicitement avant d'écrire le contenu du tableau. Cet affichage est consititué de crochets ([]) autour de chaque limite basse et haute d'une dimension avec un délimiteur deux-points (:) entre les deux. L'affichage des dimensions du tableau est suivie par un signe d'égalité (=). 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)
   

La routine de sortie du tableau inclut les dimensions explicites dans le resultat uniquement lorsqu'au moins une limite basse est différente de 1.

Si la valeur écrite pour un élément est NULL (toute variante), l'élément est considéré NULL. La présence de guillemets ou d'antislashs désactive ce fonctionnement et autorise la saisie de la valeur litérale de la chaîne « NULL ». De plus, pour une compatibilité ascendante avec les versions antérieures à la version 8.2 de PostgreSQL™, le paramètre de configuration array_nulls doit être désactivé (off) pour supprimer la reconnaissance de NULL comme un NULL.

Comme indiqué précédemment, lors de l'écriture d'une valeur de tableau, des guillemets doubles peuvent être utilisés autour de chaque élément individuel du tableau. Il faut le faire si leur absence autour d'un élément induit en erreur l'analyseur de tableau. Par exemple, les éléments contenant des crochets, virgules (ou tout type de données pour le caractère délimiteur correspondant), guillemets doubles, antislashs ou espace (en début comme en fin) doivent avoir des guillemets doubles. Les chaînes vides et les chaînes NULL doivent aussi être entre guillemets. Pour placer un guillemet double ou un antislash dans une valeur d'élément d'un tableau, on utilise la syntaxe d'échappement des chaînes en le précédant d'un antislash. Alternativement, il est possible de se passer de guillemets et d'utiliser l'échappement par antislash pour protéger tous les caractères de données qui seraient autrement interprétés en tant que caractères de syntaxe de tableau.

Des espaces peuvent être ajoutées avant un crochet gauche ou après un crochet droit. Comme avant tout élément individuel. Dans tous ces cas-là, les espaces sont ignorées. En revanche, les espaces à l'intérieur des éléments entre guillemets doubles ou entourées de caractères autres que des espaces ne sont pas ignorées.