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.
Tableau F.31 montre les fonctions fournies par le
module tablefunc
.
Tableau F.31. Fonctions tablefunc
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)
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.
Voir aussi la commande \crosstabview
dans psql. Elle fournit des fonctionnalités
similaires à crosstab()
.
crosstabN
(text)
crosstabN
(text sql)
Les fonctions crosstab
sont des exemples de configuration de fonctions d'emballage pour la
fonction généraliste N
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 disponibles dans le fichier
contrib/tablefunc/tablefunc--1.0.sql
. 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;
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
.
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 explique les paramètres.
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.
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)
Joe Conway