PostgreSQLLa base de données la plus sophistiquée au monde.

F.33. tablefunc

Le module tablefunc inclut plusieurs fonctions permettant de renvoyer des tables (c'est-à-dire plusieurs lignes). Ces fonctions sont utiles directement et comme exemples sur la façon d'écrire des fonctions C qui renvoient plusieurs lignes.

F.33.1. Fonctions

Tableau F.31. Fonctions tablefunc

Fonction Retour Description
normal_rand(int numvals, float8 mean, float8 stddev) setof float8 Renvoie un ensemble de valeurs float8 normalement distribuées
crosstab(text sql) setof record Renvoie une « table pivot » contenant les noms des lignes ainsi que N colonnes de valeur, où N est déterminé par le type de ligne spécifié par la requête appelant
crosstabN(text sql) setof table_crosstab_N Produit une « table pivot » contenant les noms des lignes ainsi que N colonnes de valeurs. crosstab2, crosstab3 et crosstab4 sont prédéfinies mais vous pouvez créer des fonctions crosstabN supplémentaires de la façon décrite ci-dessous
crosstab(text source_sql, text category_sql) setof record Produit une « table pivot » avec les colonnes des valeurs spécifiées par une autre requête
crosstab(text sql, int N) setof record

Version obsolète de crosstab(text). Le paramètre N est ignoré car le nombre de colonnes de valeurs est toujours déterminé par la requête appelante

connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) setof record Produit une représentation d'une structure hiérarchique en arbre

F.33.1.1. normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
    

normal_rand produit un ensemble de valeurs distribuées au hasard (distribution gaussienne).

numvals est le nombre de valeurs que la fonction doit renvoyer. mean est la moyenne de la distribution normale des valeurs et stddev est la déviation standard de la distribution normale des valeurs.

Par exemple, cet appel demande 1000 valeurs avec une moyenne de 5 et une déviation standard de 3 :

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)
    

F.33.1.2. crosstab(text)

crosstab(text sql)
crosstab(text sql, int N)
   

La fonction crosstab est utilisé pour créer un affichage « pivot » où les données sont listées de gauche à droite plutôt que de haut en bas. Par exemple, avec ces données

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...
    

l'affiche ressemble à ceci

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...
    

La fonction crosstab prend un paramètre texte qui est une requête SQL produisant des données brutes formatées de la façon habituelle et produit une table avec un autre formatage.

Le paramètre sql est une instruction SQL qui produit l'ensemble source des données. Cette instruction doit renvoyer une colonne row_name, une colonne category et une colonne value. N est un paramètre obsolète, ignoré quand il est fourni (auparavant, il devait correspondre au nombre de colonnes de valeurs en sortie, mais maintenant ceci est déterminé par la requête appelant).

Par exemple, la requête fournie peut produire un ensemble ressemblant à ceci :

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

La fonction crosstab déclare renvoyer un setof record, donc les noms et types réels des colonnes doivent être définis dans la clause FROM de l'instruction SELECT appelante. Par exemple : statement, for example:

    SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
   

Cet exemple produit un ensemble ressemblant à ceci :

           <== value  columns  ==>
row_name   category_1   category_2
 ---------+------------+------------
   row1        val1         val2
   row2        val5         val6
   

La clause FROM doit définir la sortie comme une colonne row_name (du même type que la première colonne du résultat de la requête SQL) suivie par N colonnes value (tous du même type de données que la troisième colonne du résultat de la requête SQL). Vous pouvez configurer autant de colonnes de valeurs en sortie que vous voulez. Les noms des colonnes en sortie n'ont pas d'importance en soi.

La fonction crosstab produit une ligne en sortie pour chaque groupe consécutif de lignes en entrée avec la même valeur row_name. Elle remplit les colonnes de value, de gauche à droite, avec les champs value provenant de ces lignes. S'il y a moins de lignes dans un groupe que de colonnes value en sortie, les colonnes supplémentaires sont remplies avec des valeurs NULL ; s'il y a trop de ligne, les colonnes en entrée supplémentaires sont ignorées.

En pratique, la requête SQL devrait toujours spécifier ORDER BY 1,2 pour s'assurer que les lignes en entrée sont bien ordonnées, autrement dit que les valeurs de même row_name sont placées ensemble et son correctement ordonnées dans la ligne. Notez que crosstab ne fait pas attention à la deuxième colonne du résultat de la requête ; elle est là pour permettre le tri, pour contrôler l'ordre dans lequel les valeurs de la troisième colonne apparaissent dans la page.

Voici un exemple complet :

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)
   

Vous pouvez toujours éviter d'avoir à écrire une clause FROM pour définir les colonnes en sortie, en définissant une fonction crosstab personnalisée qui a le type de ligne désiré en sortie en dur dans sa définition. Ceci est décrit dans la prochaine section. Une autre possibilité est d'embarquer la clause FROM requise dans la définition d'une vue.

F.33.1.3. crosstabN(text)

crosstabN(text sql)
    

Les fonctions crosstabN sont des exemples de configuration de fonctions d'emballage pour la fonction généraliste crosstab. Cela vous permet de ne pas avoir à écrire les noms et types des colonnes dans la requête SELECT appelante. Le module tablefunc inclut crosstab2, crosstab3 et crosstab4, dont les types de ligne en sortie sont définis ainsi :

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);
    

Du coup, ces fonctions peuvent être utilisées directement quand la requête en entrée produit des colonnes row_name et value de type text, et que vous voulez 2, 3 ou 4 colonnes de valeur en sortie. Autrement, elles se comportent exactement la fonction crosstab décrite précédemment.

L'exemple de la section précédente pourrait aussi fonctionner ainsi :

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');
    

Ces fonctions sont fournies principalement comme exemples. Vous pouvez créer vos propres types de retour et fonctions basées sur la fonction crosstab(). Il existe deux façons de le faire :

  • Créer un type composite décrivant les colonnes désirées en sortie, similaire aux exemples du script d'installation. Ensuite, définir un nom de fonction unique acceptant un paramètre de type text et renvoyant setof nom_de_votre_type, mais renvoyant à la fonction C crosstab. Par exemple, si votre source de données produit des noms de ligne qui sont de type text, et des valeurs qui sont de type float8, et que vous voulez cinq colonnes de valeurs :

          CREATE TYPE my_crosstab_float8_5_cols AS (
              my_row_name text,
              my_category_1 float8,
              my_category_2 float8,
              my_category_3 float8,
              my_category_4 float8,
              my_category_5 float8
          );
    
          CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
            RETURNS setof my_crosstab_float8_5_cols
            AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
          
    
  • Utiliser des paramètres OUT pour définir implicitement le type en retour. Le même exemple pourrait s'écrire ainsi :

          CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(IN text,
              OUT my_row_name text,
              OUT my_category_1 float8,
              OUT my_category_2 float8,
              OUT my_category_3 float8,
              OUT my_category_4 float8,
              OUT my_category_5 float8)
            RETURNS setof record
            AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
          
    

F.33.1.4. crosstab(text, text)

crosstab(text source_sql, text category_sql)
   

La limite principale de la forme à un paramètre de crosstab est qu'elle traite toutes les valeurs d'un groupe de la même façon, en insérant chaque valeur dans la première colonne disponible. Si vous voulez les colonnes de valeur correspondant à des catégories spécifiques de données, et que certains groupes n'ont pas de données pour certaines des catégories, alors cela ne fonctionne pas. La forme à deux paramètres de la fonction crosstab gère ce cas en fournissant une liste explicite des catégories correspondant aux colonnes en sortie.

source_sql est une instruction SQL qui produit l'ensemble source des données. Cette instruction doit renvoyer une colonne row_name, une colonne category et une colonne value. Elle pourrait aussi avoir une ou plusieurs colonnes « extra ». La colonne row_name doit être la première. Les colonnes category et value doivent être les deux dernières colonnes, dans cet ordre. Toutes les colonnes entre row_name et category sont traitées en « extra ». Les colonnes « extra » doivent être les mêmes pour toutes les lignes avec la même valeur row_name.

Par exemple, source_sql produit un ensemble ressemblant à ceci :

 SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

     row_name    extra_col   cat    value
    ----------+------------+-----+---------
      row1         extra1    cat1    val1
      row1         extra1    cat2    val2
      row1         extra1    cat4    val4
      row2         extra2    cat1    val5
      row2         extra2    cat2    val6
      row2         extra2    cat3    val7
      row2         extra2    cat4    val8
   

category_sql est une instruction SQL qui produit l'ensemble des catégories. Cette instruction doit renvoyer seulement une colonne. Cela doit produire au moins une ligne, sinon une erreur sera générée. De plus, cela ne doit pas produire de valeurs dupliquées, sinon une erreur sera aussi générée. category_sql doit ressembler à ceci :

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4
   

La fonction crosstab déclare renvoyer setof record, donc les noms et types réels des colonnes en sortie doivent être définis dans la clause FROM de la requête SELECT appelante, par exemple :

    SELECT * FROM crosstab('...', '...')
      AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
   

Ceci produira un résultat ressemblant à ceci :

                              <==  value  columns   ==>
           row_name   extra   cat1   cat2   cat3   cat4
           ---------+-------+------+------+------+------
             row1     extra1  val1   val2          val4
             row2     extra2  val5   val6   val7   val8
   

La clause FROM doit définir le bon nombre de colonnes en sortie avec les bon types de données. S'il y a N colonnes dans le résultat de la requête source_sql, les N-2 premiers d'entre eux doivent correspondre aux N-2 premières colonnes en sortie. Les colonnes restantes en sortie doivent avoir le type de la dernière colonne du résultat de la requête The remaining output columns source_sql, et il doit y en avoir autant que de lignes dans le résultat de la requête category_sql.

La fonction crosstab produit une ligne en sortie pour chaque groupe consécutif de lignes en entrée avec la même valeur row_name. La colonne en sortie row_name ainsi que toutes colonnes « extra » sont copiées à partir de la première ligne du groupe. Les colonnes value en sortie sont remplies avec les champs value à partir des lignes ayant une correspondance avec des valeurs category. Si la category d'une ligne ne correspond pas à une sortie de la requête category_sql, sa value est ignorée. Les colonnes en sortie dont la catégorie correspondante est absente de toute ligne en entrée du groupe sont remplies avec des valeurs NULL.

En pratique, la requête source_sql doit toujours spécifier ORDER BY 1 pour s'assurer ques les valeurs du même row_name sont assemblées. Néanmoins, l'ordre des catégories dans un groupe n'est pas important. De plus, il est essentiel que l'ordre du résultat de la requête category_sql corresponde à l'ordre des colonnes spécifiées en sortie.

Voici deux exemples complets :

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)
   
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)
   

Vous pouvez créer des fonctions prédéfinies pour éviter d'avoir à écrire les noms et types des colonnes en résultat dans chaque requête. Voir les exemples dans la section précédente. La fonction C sous-jacente pour cette forme de crosstab est appelée crosstab_hash.

F.33.1.5. connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])
   

La fonction connectby réalise un affichage de données hiérarchiques stockées dans une table. La table doit avoir un champ clé qui identifie de façon unique les lignes et un champ clé qui référence le parent de chaque ligne. connectby peut afficher le sous-arbre à partir de n'importe quelle ligne.

Tableau F.32. Paramètres connectby

Paramètre Description
relname Nom de la relation source
keyid_fld Nom du champ clé
parent_keyid_fld Nom du champ clé du parent
orderby_fld Nom du champ des autres relations (optionnel)
start_with Valeur de la clé de la ligne de début
max_depth Profondeur maximum pour la descente, ou zéro pour une profondeur illimitée
branch_delim Chaîne pour séparer les clés des branches (optionnel)

Les champs clé et clé du parent peuvent être de tout type mais ils doivent être du même type. Notez que la valeur start_with doit être saisi comme une chaîne de caractères, quelque soit le type du champ clé.

La fonction connectby déclare renvoyer un setof record, donc les noms et types réels des colonnes en sortie doivent être définis dans la clause FROM de l'instruction SELECT appelante, par exemple :

    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
      AS t(keyid text, parent_keyid text, level int, branch text, pos int);
    

Des deux premières colonnes en sortie sont utilisées pour la clé de la ligne en cours et la clé de son parent ; elles doivent correspondre au type du champ clé de la table. La troisième colonne est la profondeur de l'arbre est doit être du type integer. Si un paramètre branch_delim est renseigné, la prochaine colonne en sortie est l'affichage de la branche et doit être de type text. Enfin, si le paramètre orderby_fld est renseigné, la dernière colonne en sortie est un numéro de série et doit être de type integer.

La colonne « branch » en sortie affiche le chemin des clés utilisé pour atteindre la ligne actuelle. Les clés sont séparées par la chaîne branch_delim spécifiée. Si l'affichage des branches n'est pas voulu, omettez le paramètre branch_delim et la colonne branche dans la liste des colonnes en sortie. and the branch column in the output column list.

Si l'ordre des relations du même parent est important, incluez le paramètre orderby_fld pour indiquer par quel champ ordonner les relations. Ce champs doit être de tout type de données triable. La liste des colonnes en sortie doit inclure une colonne numéro de série de type integer si, et seulement si, orderby_fld est spécifiée.

Les paramètres représentant table et noms de champs sont copiés tels quel dans les requêtes SQL que connectby génère en interne. Du coup, ajoutez des guillemets doubles si les noms utilisent majuscules et minuscules ou s'ils contiennent des caractères spéciaux. Vous pouvez aussi avoir besoin de qualifier le nom de la table avec le nom du schéma.

Dans les grosses tables, les performances seront faibles sauf si un index est créé sur le champ clé parent.

Il est important que la chaîne branch_delim n'apparaisse pas dans les valeurs des clés, sinon connectby pourrait rapporter des erreurs de récursion infinie totalement erronées. Notez que si branch_delim n'est pas fourni, une valeur par défaut ~ est utilisé pour des raisons de détection de récursion.

Voici un exemple :

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)
    

F.33.2. Auteur

Joe Conway