Fonction generate_series() de PostgreSQL

Il y a une fonction de PostgreSQL très peu documentée et pourtant très pratique qui se nomme generate_series(). Il s'agit d'une fonction qui permet de générer des séries de nombres entiers, un peu à la manière d'une boucle "for" où on indique la valeur de départ, la valeur maximale et de façon optionnelle le saut (step) entre chaque itération (1 par défaut).

Par exemple, pour obtenir 5 enregistrements (rows) allant de 1 à 5 :

SELECT * FROM generate_series(1,5)
--------------------------------------------------------
1
2
3
4
5

On peut aussi obtenir un jeu de résultats en utilisant un saut, positif ou négatif, qui sera passé comme troisième argument (optionnel, la valeur par défaut est +1) :

-- obtenir 0, 10, 20, 30, ... 90, 100
SELECT * FROM generate_series(0, 100, 10)

Dans mon cas, j'y ai trouvé une utilité lorsque j'ai eu à programmer un calendrier d'événements s'échelonant sur plusieurs jours, où la modélisation des dates n'était pas normalisée dans la base de données. Pour exposer l'idée de façon simpliste, on définira une table et un événement, par exemple la Conférence PHP Québec 2009, qui aura lieu du 4 au 6 mars 2009 (durée de 3 jours).

-- création de la table
CREATE TABLE events
(
pk_event integer serial,
eventname character varying(100),
start_date date,
nb_days integer
)

-- Insertion de l'événement (ici, la séquence sera auto-incrémentée et aura la valeur 1)
INSERT INTO events (eventname, start_date, nb_days)
VALUES ('Conférence PHP Québec 2009', '2009-03-04', 3);

Si on souhaite obtenir une liste calculée des trois dates, on pourra exécuter une requête SQL semblable à celle-ci :

SELECT start_date + generate_series(0, nb_days-1) as event_date
FROM events
WHERE pk_event = 1;

Résultat :
2009-03-04
2009-03-05
2009-03-06

C'est un peu comme si on disait en pseudo-code :
nb_days_max = (nb_days - 1) // 2
date = start_date; // '2008-03-04'
for (days = 0 ; days <= nb_days_max ; days++ ) {
// days = 0, 1, 2
echo (date + days);
}

Dans cette requête, j'utilise le champ "start_date" de l'événement comme valeur de départ, à laquelle j'ajoute un des nombres de la série générée qui correspond au nombre de jours. Comme l'événement débute le 4 mars et que cette date est déjà définie dans la base de données, on aura qu'à ajouter 0 à la date initiale pour la conserver telle quelle (première valeur de la série). La dernière date sera calculée en ajoutant 2 jours à la date initiale lors de la dernière itération de generate_series.

En fouillant un peu, je me suis aperçu qu'il n'existait pas d'implémentation native pour d'autres types de bases de données mais j'ai trouvé des scripts de définitions de fonctions similaires pour SQL Server et Oracle.

Une autre utilisation pratique de generate_series() serait de pouvoir effectuer des requêtes sur les listes de codes de statut HTTP ou de tout type de classement inspiré de la classification numérique de Dewey.

Tags: PostgreSQL