31.10. Agr�gats d�finis par l'utilisateur

Dans PostgreSQL, les fonctions d'agr�gat sont exprim�es comme des valeurs d'�tat et des fonctions d'�tat de transition. Autrement dit, un agr�gat peut �tre d�fini en termes d'�tat modifi� chaque fois qu'une entr�e est trait�e. Pour d�finir une nouvelle fonction d'agr�gat, on choisit un type de donn�e pour la valeur d'�tat, une valeur initiale pour l'�tat et une fonction de transition d'�tat. La fonction de transition d'�tat est simplement une fonction ordinaire qui pourrait aussi bien �tre utilis�e hors du contexte de l'agr�gat. Une fonction finale peut �galement �tre sp�cifi�e, au cas o� le r�sultat d�sir� pour l'agr�gat est diff�rent des donn�es devant �tre conserv�es comme valeur courante de l'�tat.

Ainsi, en plus des types de donn�es de l'argument et du r�sultat vus par l'utilisateur, il existe un type de donn�e pour la valeur d'�tat interne qui peut �tre diff�rent de ces deux derniers.

Si nous d�finissons un agr�gat qui n'utilise pas de fonction finale, nous avons un agr�gat qui calcule pour chaque ligne une fonction des valeurs de colonnes. sum est un exemple de cette sorte d'agr�gat. sum commence � z�ro et ajoute toujours la valeur de la ligne courante � son total en cours. Par exemple, si nous voulons faire un agr�gat sum pour op�rer sur un type de donn�e pour des nombres complexes, nous avons seulement besoin de la fonction d'addition pour ce type de donn�e. La d�finition de l'agr�gat sera :

CREATE AGGREGATE somme_complexe (
    sfunc = ajout_complexe,
    basetype = complexe,
    stype = complexe,
    initcond = '(0,0)'
);

SELECT somme_complexe(a) FROM test_complexe;

 somme_complexe
----------------
     (34,53.9)

(Dans la pratique, nous aurions seulement nomm� l'agr�gat sum et laiss� PostgreSQL d�terminer quel genre de somme appliquer � une colonne de type complexe.)

La d�finition pr�c�dente de sum renverra z�ro (la condition d'�tat initial) s'il n'y a pas de valeurs d'entr�e non NULL. Peut-�tre d�sirons-nous que dans ce cas elle retourne NULL — le standard SQL pr�voit que la fonction sum se comporte ainsi. Nous pouvons faire ceci simplement en omettant l'instruction initcond, de sorte que la condition d'�tat initial soit NULL. Ordinairement, ceci signifierait que sfunc aurait � v�rifier l'entr�e d'une condition d'�tat NULL, mais pour la fonction sum et quelques autres agr�gats simples comme max et min, il suffit d'ins�rer la premi�re valeur d'entr�e non NULL dans la variable d'�tat et ensuite de commencer � appliquer la fonction de transition d'�tat � la seconde valeur non NULL. PostgreSQL fera cela automatiquement si la condition initiale est NULL et si la fonction de transition est marqu�e <<�strict�>> (c'est-�-dire qu'elle ne doit pas �tre appel�e pour des entr�es NULL).

Un autre comportement par d�faut d'une fonction de transition <<�strict�>> est que la valeur d'�tat pr�c�dente est gard�e inchang�e chaque fois qu'une entr�e NULL est rencontr�e. Ainsi, les valeurs NULL sont ignor�es. Si vous avez besoin d'un autre comportement pour les entr�es NULL, vous devez juste ne pas d�finir votre fonction de transition comme <<�strict�>> et la coder pour v�rifier les entr�es NULL et faire le n�cessaire.

avg (average = moyenne) est un exemple plus compliqu� d'agr�gat. Il demande deux �tat courants : la somme des entr�es et le compte du nombre d'entr�es. Le r�sultat final est obtenu en divisant ces quantit�s. La moyenne est typiquement impl�ment�e en utilisant comme valeur d'�tat un tableau de deux �l�ments. Par exemple, l'impl�mentation int�gr�e de avg(float8) ressemble � :

CREATE AGGREGATE avg (
    sfunc = float8_accum,
    basetype = float8,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0}'
);

Les fonctions d'agr�gat peuvent utiliser des fonctions d'�tat de transition ou des fonctions finales polymorphes, de sorte que les m�mes fonctions peuvent �tre utilis�es pour impl�menter de multiples agr�gats. Voir la Section 31.2.1 pour une explication des fonctions polymorphes. Pour aller encore plus loin, la fonction d'agr�gat elle-m�me peut �tre sp�cifi�e avec un type de base et un type d'�tat polymorphes, permettant ainsi � une unique d�finition de fonction de servir pour de multiples types de donn�es d'entr�e. Voici un exemple d'agr�gat polymorphe :

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

Ici, le type d'�tat effectif pour n'importe quel appel d'agr�gat est le type tableau, ayant comme �l�ments le type effectif d'entr�e.

Voici le r�sultat quand on utilise deux types de donn�e effectifs diff�rents comme arguments :

SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute WHERE attnum > 0
AND attrelid = 'pg_user'::regclass GROUP BY attrelid;
 attrelid |                                 array_accum
----------+-----------------------------------------------------------------------------
 pg_user  | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid)
FROM pg_attribute WHERE attnum > 0
AND attrelid = 'pg_user'::regclass GROUP BY attrelid;
 attrelid |         array_accum
----------+------------------------------
 pg_user  | {19,23,16,16,16,25,702,1009}
(1 row)

Pour plus de d�tails, voyez la commande CREATE AGGREGATE.