Dans PostgreSQL™, les fonctions d'agrégat sont exprimées comme des valeurs d'état et des fonctions de transition d'état. C'est-à-dire qu'un agrégat opère en utilisant une valeur d'état qui est mis à jour à chaque ligne 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 une fonction ordinaire qui peut être utilisée hors agrégat. Une fonction finale peut également être spécifiée pour le cas où le résultat désiré comme agrégat est différent des données conservées comme valeur d'état courant.
Ainsi, en plus des types de données d'argument et de résultat vus par l'utilisateur, il existe un type de données pour la valeur d'état interne qui peut être différent des deux autres.
Un agrégat qui n'utilise pas de fonction finale est un agrégat qui utilise pour chaque ligne une fonction dépendante des valeurs de colonnes. sum en est un exemple. sum débute à zéro et ajoute la valeur de la ligne courante à son total en cours. Par exemple, pour obtenir un agrégat sum qui opère sur un type de données nombres complexes, il suffira décrire la fonction d'addition pour ce type de donnée. La définition de l'agrégat sera :
CREATE AGGREGATE somme (complex)
(
sfunc = ajout_complexe,
stype = complexe,
initcond = '(0,0)'
);
SELECT somme(a) FROM test_complexe;
somme
-----------
(34,53.9)
(Notez que nous nous reposons sur une surcharge de fonction : il existe plus d'un agrégat nommé sum mais PostgreSQL™ trouve le type de somme s'appliquant à une colonne de type complex.)
La définition précédente de sum retournera zéro (la condition d'état initial) s'il n'y a que des valeurs d'entrée NULL. Dans ce cas, on peut souhaiter qu' elle retourne NULL -- le standard SQL prévoit que la fonction sum se comporte ainsi. Cela peut être obtenu par l'omission de l'instruction initcond, de sorte que la condition d'état initial soit NULL. Dans ce cas, sfunc vérifie l'entrée d'une condition d'état NULL mais, pour 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 d'appliquer la fonction de transition d'état à partir de la seconde valeur non NULL. PostgreSQL™ fait cela automatiquement si la condition initiale est NULL et si la fonction de transition est marquée « strict » (elle n'est pas appelée pour les entrées NULL).
Par défaut également, pour les fonctions de transition « strict », la valeur d'état précédente reste inchangée pour une entrée NULL. Les valeurs NULL sont ainsi ignorées. Pour obtenir un autre comportement, il suffit de ne pas déclarer la fonction de transition « strict ». À la place, codez-la de façon à ce qu'elle vérifie et traite les entrées NULL.
avg (average = moyenne) est un exemple plus complexe d'agrégat. Il demande deux états courants : la somme des entrées et le nombre d'entrées. Le résultat final est obtenu en divisant ces quantités. La moyenne est typiquement implantée en utilisant comme valeur d'état un tableau. Par exemple, l'implémentation intégrée de avg(float8) ressemble à :
CREATE AGGREGATE avg (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0,0}'
);
(float8_accum nécessite un tableau à trois éléments, et non pas seulement deux, car il accumule la somme des carrés, ainsi que la somme et le nombre des entrées. Cela permet son utilisation pour d'autres agrégats que avg.)
Les fonctions d'agrégat peuvent utiliser des fonctions d'état transitionnelles ou des fonctions finales polymorphes. De cette façon, les mêmes fonctions peuvent être utilisées pour de multiples agrégats. Voir la Section 35.2.5, « Types et fonctions polymorphes » pour une explication des fonctions polymorphes. La fonction d'agrégat elle-même peut être spécifiée avec un type de base et des types d'état polymorphes, ce qui permet ainsi à une unique définition de fonction de servir pour de multiples types de données en entrée. Voici un exemple d'agrégat polymorphe :
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
Dans ce cas, le type d'état effectif pour tout appel d'agrégat est le type tableau avec comme éléments le type effectif d'entrée. Le comportement de l'agrégat est de concaténer toutes les entrées dans un tableau de ce type. (Note : l'agrégat array_agg fournit une fonctionnalité similaire, avec de meilleures performances que ne pourrait avoir cette définition.)
Voici le résultat pour deux types de données différents en arguments :
SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute WHERE attnum > 0
AND attrelid = 'pg_tablespace'::regclass GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------------------
pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)
SELECT attrelid::regclass, array_accum(atttypid::regtype)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------
pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)
Une fonction écrite en C peut détecter si elle est appelée en tant que fonction de transition ou en tant que fonction finale d'un agrégat en appelant AggCheckCallContext, par exemple :
if (AggCheckCallContext(fcinfo, NULL))
Une raison de surveiller ceci est que, si le retour de cette fonction vaut true pour une fonction de transition, la première valeur doit être une valeur de transition temporaire et peut du coup être modifiée en toute sûreté sans avoir à allouer une nouvelle copie. Voir int8inc() pour un exemple. (C'est le seul cas où une fonction peut modifier en toute sécurité un argument passé en référence. En particulier, les fonctions finales d'agrégat ne doivent pas modifier leur arguments dans tous les cas car, dans certains cas, elles seront ré-exécutées sur la même valeur de transition finale.)
Pour de plus amples détails, on se réfèrera à la commande CREATE AGGREGATE(7).
Fonctions en langage C
