PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 16.4 » Langage SQL » Fonctions et opérateurs » Fonctions renvoyant des ensembles

9.25. Fonctions renvoyant des ensembles #

Cette section décrit les fonctions qui renvoient potentiellement plus d'une ligne. Les fonctions les plus fréquemment utilisées de ce type sont celles générant des séries, comme détaillées dans Tableau 9.65 et Tableau 9.66. D'autres, plus spécialisées, sont décrites ailleurs dans ce manuel. Voir Section 7.2.1.4 pour des façons de combiner plusieurs fonctions renvoyant des ensembles.

Tableau 9.65. Fonctions générant des séries

Fonction

Description

generate_series ( start integer, stop integer [, step integer ] ) → setof integer

generate_series ( start bigint, stop bigint [, step bigint ] ) → setof bigint

generate_series ( start numeric, stop numeric [, step numeric ] ) → setof numeric

Génère une série de valeurs à partir de start jusqu'à stop, avec un pas de step. step a 1 pour valeur par défaut.

generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp

generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval [, timezone text ] ) → setof timestamp with time zone

Génère une série de valeurs à partir de start jusqu'à stop, avec un pas de step. Dans la syntaxe sensible aux fuseaux horaires, les heures du jour et les ajustements de changement d'heure sont calculés suivant le fuseau horaire nommé par l'argument timezone ou indiqué par le paramètre TimeZone si cet argument est omis.


Quand step est positif, aucune ligne n'est renvoyée si start est supérieure à stop. Par contre, quand step est négatif negative, aucune ligne n'est renvoyée si start est inférieur à stop. Aucune ligne n'est renvoyé si au moins une entrée est NULL. Si step vaut zéro, c'est considéré comme une erreur. Voici quelques exemples :

SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

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

SELECT * FROM generate_series(4,3);
 generate_series
-----------------
(0 rows)

SELECT generate_series(1.1, 4, 1.3);
 generate_series
-----------------
             1.1
             2.4
             3.7
(3 rows)

-- cet exemple se base sur l'opérateur date-plus-integer :
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-04 12:00', '10 hours');
   generate_series
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00
 2008-03-03 02:00:00
 2008-03-03 12:00:00
 2008-03-03 22:00:00
 2008-03-04 08:00:00
(9 rows)

-- cet exemple suppose que TimeZone est configuré à UTC ; notez la transition DST:
SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
                              '2001-11-01 00:00 -05:00'::timestamptz,
                              '1 day'::interval, 'America/New_York');
    generate_series
------------------------
 2001-10-22 04:00:00+00
 2001-10-23 04:00:00+00
 2001-10-24 04:00:00+00
 2001-10-25 04:00:00+00
 2001-10-26 04:00:00+00
 2001-10-27 04:00:00+00
 2001-10-28 04:00:00+00
 2001-10-29 05:00:00+00
 2001-10-30 05:00:00+00
 2001-10-31 05:00:00+00
 2001-11-01 05:00:00+00
(11 rows)

Tableau 9.66. Fonctions générant des indices

Fonction

Description

generate_subscripts ( array anyarray, dim integer ) → setof integer

Génère une série comprenant les indices valides de la dim-ème dimension du tableau fourni.

generate_subscripts ( array anyarray, dim integer, reverse boolean ) → setof integer

Génère une série comprenant les indices valides de la dim-ième dimension du tableau fourni. Quand reverse vaut true, renvoie la série dans l'ordre inverse.


generate_subscripts est une fonction de facilité qui génère l'ensemble d'indices valides pour la dimension donnée pour le tableau. Aucune ligne n'est renvoyée pour les tableaux qui n'ont pas la dimension demandée ou si une des lignes vaut NULL. Voici quelques exemples :

-- utilisation basique :
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
 s
---
 1
 2
 3
 4
(4 rows)

-- à partir d'un tableau, l'indice et la valeur indicée
-- nécessite une sous-requête :
SELECT * FROM arrays;
         a
--------------------
 {-1,-2}
 {100,200,300}
(2 rows)

SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
     array     | subscript | value
---------------+-----------+-------
 {-1,-2}       |         1 |    -1
 {-1,-2}       |         2 |    -2
 {100,200,300} |         1 |   100
 {100,200,300} |         2 |   200
 {100,200,300} |         3 |   300
(5 rows)

-- déballer un tableau 2D :
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
select $1[i][j]
   from generate_subscripts($1,1) g1(i),
        generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
 unnest2
---------
       1
       2
       3
       4
(4 rows)

Quand une fonction dans la clause FROM a pour suffixe WITH ORDINALITY, une colonne bigint est ajoutée aux colonnes en sortie de la fonction, commençant à 1 et s'incrémentant de 1 pour chaque ligne de la sortie de la fonction. Ceci est plus utile dans le cas de fonctions renvoyant des ensembles comme unnest().

-- fonction renvoyant un ensemble WITH ORDINALITY:
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
       ls        | n
-----------------+----
 pg_serial       |  1
 pg_twophase     |  2
 postmaster.opts |  3
 pg_notify       |  4
 postgresql.conf |  5
 pg_tblspc       |  6
 logfile         |  7
 base            |  8
 postmaster.pid  |  9
 pg_ident.conf   | 10
 global          | 11
 pg_xact         | 12
 pg_snapshots    | 13
 pg_multixact    | 14
 PG_VERSION      | 15
 pg_wal          | 16
 pg_hba.conf     | 17
 pg_stat_tmp     | 18
 pg_subtrans     | 19
(19 rows)