Group By dans un tableau avec PostgreSQL

Le problème qui se pose est le suivant: certaines requêtes doivent retourner un identifiant, et une liste de langues (2 à n) qui correspondent à l'identifiant. Comment agréger cette liste de taille variable et l'exploiter dans le reste des fonctions SQL? Grâce aux tableaux!

Pour les identifiants on a la table X:

Et pour les langues, la table LANGS:

Il se trouve que PostgreSQL gère un type composite particulièrement pratique, les tableau (ARRAY). Un petit exemple:

$ select 5, '{1, 2, 3, 4}';
 ?column? |   ?column?   
----------+--------------
        5 | {1, 2, 3, 4}
(1 row)

Les tableaux ayant toute une série de fonction associée, c'est l'idéal pour gérer ce genre de données variables. Reste qu'il n'y a pas, par défaut, de fonction d'agrégationdans un tableau (donc pas de group by dans un array).

Heureusement, PostgreSQL nous permet de créer des fonctions d'agrégation, et c'est même mieux, la documentation nous dit comment résoudre notre problème!

On commence par créer la fonction d'agrégation:

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

ou (pour PostgreSQL <= 8.1):

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

Et on peut agréger des données variable dans notre tableau:

SELECT X.id, array_accum(LANGS.all_langs)
FROM X
     JOIN LANGS ON (X.id = LANGS.X_id)
GROUP BY X.id

Allez plus loin ', '.join(array)

Nous avons vu une utilisation bien jolie des tableaux et je vous propose de voir une autre fonctionnalité cool pour vos fonctions SQL. En Python, on peut utiliser join() sur une chaîne de caractère pour la concatainer de façon intélligente:

>>> liste_langue = ['fr_fr', 'en_us', 'es']
>>> print ', '.join(liste_langue)
"fr_fr, en_us, es"

Avec PostgreSQL et les tableaux, on fais exactement la même chose grâce à la fonction array_to_string().

Si je reprend l'exemple précédent, on peux faire

SELECT X.id, array_to_string(array_accum(LANGS.all_langs), ', ') as langs
FROM X
     JOIN LANGS ON (X.id = LANGS.X_id)
GROUP BY X.id

qui nous donne:

 X.id     |   langs   
----------+-------------------
        1 | fr_fr, en_us
        2 | fr_fr, es, en_gb

(1 row)