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

9.24. Fonctions retournant des ensembles

Cette section décrit des fonctions qui peuvent renvoyer plus d'une ligne. Les fonctions les plus utilisées dans cette classe sont celles générant des séries de données, comme détaillé dans Tableau 9.61 et Tableau 9.62. D'autres fonctions 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 de lignes.

Tableau 9.61. Fonctions de génération de séries

FonctionType d'argumentType de retourDescription
generate_series (début, fin)int, bigint ou numericsetof int, setof bigint ou setof numeric (même type que l' argument) Produit une série de valeurs, de début à fin avec un incrément de un.
generate_series (début, fin, pas)int, bigint ou numericsetof int, setof bigint ou setof numeric (même type que l'argument) Produit une série de valeurs, de début à fin avec un incrément de pas.
generate_series(début, fin, pas interval)timestamp ou timestamp with time zonesetof timestamp ou setof timestamp with time zone (identique au type de l'argument) Génère une série de valeurs, allant de début à fin avec une taille pour chaque étape de pas

Quand pas est positif, aucune ligne n'est renvoyée si début est supérieur à fin. À l'inverse, quand pas est négatif, aucune ligne n'est renvoyée si début est inférieur à fin. De même, aucune ligne n'est renvoyée pour les entrées NULL. Une erreur est levée si pas vaut zéro.

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-entier
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)

Tableau 9.62. Fonctions de génération d'indices

NomType de retourDescription
generate_subscripts(array anyarray, dim int)setof int Génère une série comprenant les indices du tableau donné.
generate_subscripts(array anyarray, dim int, reverse boolean)setof int Génère une série comprenant les indices du tableau donné. Quand reverse vaut true, la série est renvoyée en ordre inverse.

generate_subscripts est une fonction qui génère un ensemble d'indices valides pour la dimension indiquée du tableau fourni. Aucune ligne n'est renvoyée pour les tableaux qui n'ont pas la dimension requise ou pour les tableaux NULL (mais les indices valides sont renvoyés pour les éléments d'un tableau NULL). Quelques exemples suivent :

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

-- presenting an array, the subscript and the subscripted
-- value requires a subquery
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)

-- aplatir 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 se voit ajouter la clause WITH ORDINALITY, une colonne de type bigint est ajoutée à la sortie. Sa valeur commence à 1 et s'incrémente pour chaque ligne en sortie de la fonction. Ceci est particulièrement utile dans le cas de fonctions renvoyant un ensemble de lignes comme unnest().

-- set returning function 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)