Description

CREATE AGGREGATE définit une nouvelle fonction d'agrégat. CREATE OR REPLACE AGGREGATE définit une nouvelle fonction d'agrégat ou remplace une définition existante. Quelques fonctions d'agrégat basiques et largement utilisées sont fournies dans la distribution standard ; elles sont documentées dans le Section 9.21. CREATE AGGREGATE est utilisée pour ajouter des fonctionnalités lors de la définition de nouveaux types ou si une fonction d'agrégat n'est pas fournie.

Lorsqu'une définition existante est remplacée, les types d'arguments, le type de résultat, et le nombre d'arguments directs ne peuvent être changés. La nouvelle définition doit également être du même type (agrégat ordinaire, ou d'ensemble trié, ou d'ensemble hypothétique) que l'ancien.

Si un nom de schéma est donné (par exemple, CREATE AGGREGATE monschema.monagg ...), alors la fonction d'agrégat est créée dans le schéma précisé. Sinon, elle est créée dans le schéma courant. Ce comportement est identique à la surcharge de noms de fonctions ordinaires (voir CREATE FUNCTION).

Une fonction d'agrégat simple est identifiée par son nom et son (ou ses) types de données en entrée. Deux agrégats dans le même schéma peuvent avoir le même nom s'ils opèrent sur des types différents en entrée. Le nom et le(s) type(s) de données en entrée d'un agrégat doivent aussi être distincts du nom et du type de données de toutes les fonctions ordinaires du même schéma.

Une fonction d'agrégat est réalisée à partir d'une ou deux fonctions ordinaires : une fonction de transition d'état sfonc, et une fonction de traitement final optionnelle ffonc. Elles sont utilisées ainsi :

sfonc( état-interne, nouvelle-valeur-données ) ---> prochain-état-interne
  ffonc( état-interne ) ---> valeur-agrégat
   

PostgreSQL crée une variable temporaire de type stype pour contenir l'état interne courant de l'agrégat. À chaque ligne en entrée, la valeur de l'argument de l'agrégat est calculée et la fonction de transition d'état est appelé avec la valeur d'état courante et la valeur du nouvel argument pour calculer une nouvelle valeur d'état interne. Une fois que toutes les lignes sont traitées, la fonction finale est appelée une seule fois pour calculer la valeur de retour de l'agrégat. S'il n'existe pas de fonction finale, alors la valeur d'état final est retournée en l'état.

Une fonction d'agrégat peut fournir une condition initiale, c'est-à-dire une valeur initiale pour la valeur de l'état interne. Elle est spécifiée et stockée en base comme une valeur de type text mais doit être une représentation externe valide d'une constante du type de donnée de la valeur d'état. Si elle n'est pas fournie, la valeur d'état est initialement positionnée à NULL.

Si la fonction de transition d'état est déclarée « strict », alors elle ne peut pas être appelée avec des entrées NULL. Avec une telle fonction de transition, l'exécution d'agrégat se comporte comme suit. Les lignes avec une valeur NULL en entrée sont ignorées (la fonction n'est pas appelé et la valeur de l'état précédent est conservé). Si la valeur de l'état initial est NULL, alors, à la première ligne sans valeur NULL, la première valeur de l'argument remplace la valeur de l'état, et la fonction de transition est appelée pour chacune des lignes suivantes avec toutes les valeurs non NULL en entrée. Cela est pratique pour implémenter des agrégats comme max. Ce comportement n'est possible que quand type_donnée_état est identique au premier type_donnée_argument. Lorsque ces types sont différents, une condition initiale non NULL doit être fournie, ou une fonction de transition non stricte utilisée.

Si la fonction de transition d'état n'est pas stricte, alors elle sera appelée sans condition pour chaque ligne en entrée et devra gérer les entrées NULL et les valeurs de transition NULL. Cela permet à l'auteur de l'agrégat d'avoir le contrôle complet sur la gestion des valeurs NULL par l'agrégat.

Si la fonction finale est déclarée « strict », alors elle ne sera pas appelée quand la valeur d'état finale est NULL ; à la place, un résultat NULL sera retourné automatiquement. C'est le comportement normal de fonctions strictes. Dans tous les cas, la fonction finale peut retourner une valeur NULL. Par exemple, la fonction finale pour avg renvoie NULL lorsqu'elle n'a aucune lignes en entrée.

Quelque fois, il est utile de déclarer la fonction finale comme ne retournant pas seulement la valeur d'état, mais des paramètres supplémentaires correspondant aux valeurs en entrée de l'agrégat. La raison principale pour faire ainsi est si la fonction finale est polymorphique et que le type de données de la valeur de l'état serait inadéquate pour trouver le type du résultat. Ces paramètres supplémentaires sont toujours passés en tant que valeurs NULL (et donc la fonction finale ne doit pas être stricte quand l'option FINALFUNC_EXTRA est utilisée). Néanmoins, ce sont des paramètres valides. Par exemple, la fonction finale pourrait faire usage de get_fn_expr_argtype pour identifier le type d'argument réel dans l'appel actuel.

Un agrégat peut accepter en option un mode d'agrégat glissant, comme décrit dans Section 37.12.1. Ceci requiert de spécifier les paramètres MSFUNC, MINVFUNC, et MSTYPE et, en option, les paramètres MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY, et MINITCOND. En dehors de MINVFUNC, ces paramètres fonctionnement comme les paramètres d'agrégat simple sans M ; ils définissent une implémentation séparée de l'agrégat qui inclut une fonction de transition inverse.

La syntaxe avec ORDER BY dans la liste des paramètres crée un type spécial d'agrégat appelé un agrégat d'ensemble trié. Si le mot clé HYPOTHETICAL est ajouté, un agrégat d'ensemble hypothétique est créé. Ces agrégats opèrent sur des groupes de valeurs triées, donc la spécification d'un ordre de tri en entrée est une partie essentielle d'un appel. De plus, ils peuvent avoir des arguments directs, qui sont des arguments évalués une fois seulement par agrégat plutôt qu'une fois par ligne en entrée. Les agrégats d'ensemble hypothétique sont une sous-classe des agrégats d'ensemble trié pour lesquels certains des arguments directs doivent correspondre, en nombre et type de données aux colonnes en argument de l'agrégat. Ceci permet aux valeurs de ces arguments directs d'être ajoutées à la collection de lignes en entrée de l'agrégat comme des lignes supplémentaires « hypothétiques ».

Un agrégat peut supporter en option l'agrégat partiel, comme décrit dans Section 37.12.4. Ceci requiert la spécification du paramètre COMBINEFUNC. Si le paramètre state_data_type vaut internal, il est généralement approprié de fournir les paramètres SERIALFUNC et DESERIALFUNC pour qu'un agrégat parallèle soit possible. Notez que l'agrégat doit aussi être marqué PARALLEL SAFE pour activer l'agrégation parallélisée.

Les agrégats qui se comportent comme MIN ou MAX peuvent parfois être optimisés en cherchant un index au lieu de parcourir toutes les lignes en entrée. Si un agrégat peut être optimisé, un opérateur de tri est spécifié. Dans ce cas, il est nécessaire que l'agrégat fournisse le premier élément dans l'ordre imposé par l'opérateur ; en d'autres mots :

SELECT agg(col) FROM tab;
   

doit être équivalent à :

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
   

On suppose également que l'agrégat ignore les entrées NULL et qu'il fournit un résultat NULL si et seulement s'il n'y a aucune entrée NULL. D'ordinaire, l'opérateur < d'un type de données est le bon opérateur de tri pour MIN et > celui pour MAX. L'optimisation ne prend jamais effet sauf si l'opérateur spécifié est membre de la stratégie « less than » (NdT : plus petit que) ou « greater than » (NdT : plus grand que) d'une classe d'opérateur pour un index B-tree.

Pour pouvoir créer une fonction d'agrégat, vous devez avoir le droit USAGE sur le type des arguments, le type de l'état et le type du code retour. Vous devez aussi voir le droit EXECUTE sur les fonction de support.

Paramètres

Les paramètres de CREATE AGGREGATE peuvent être écrits dans n'importe quel ordre, pas uniquement dans l'ordre illustré ci-dessus.

Notes

Dans les paramètres qui indiquent les noms de fonction de support, vous pouvez écrire un nom de schéma si nécessaire, par exemple SFUNC = public.sum. N'écrivez pas de types d'argument ici, néanmoins -- les types d'argument des fonctions de support sont déterminés avec d'autres paramètres.

D'ordinaire, les fonctions PostgreSQL sont de vraies fonctions qui ne modifient pas leurs valeurs en entrée. Néanmoins, une fonction transition d'agrégat, quand elle est utilisée dans le contexte d'un agrégat, est autorisée à tricher et à modifier son argument d'état de transition. Ceci apporte des améliorations substantielles de performance en comparaison à une copie fraîche de l'état de transition à chaque exécution.

De la même façon, quand une fonction finale d'agrégat ne modifie pas habituellement ses arguments, il n'est parfois pas pratique d'éviter la modification de l'argument d'état de transition. Un tel comportement doit être déclaré en utilisant le paramètre FINALFUNC_MODIFY. La valeur READ_WRITE indique que la fonction finale modifie l'état de transaction de façon non spécifiée. Cette valeur empêche l'utilisation de l'agrégat comme fonction de fenêtrage et il empêche aussi l'assemblage des états de transition pour les appels d'agrégat qui partagent les mêmes valeurs en entrée et de transition. La valeur SHAREABLE indique que la fonction de transition ne peut être appliquée après la fonction finale mais que plusieurs appels de la fonction finale peuvent être réalisés sur la valeur d'état de transition final. Cette valeur empêche l'utilisation de l'agrégat comme fonction de fenêtrage mais permet l'assemblage des états des transition. (l'optimisation intéressante ici n'est pas d'appliquer la même fonction finale de façon répétée mais d'appliquer plusieurs fonctions finales différentes à la même valeur d'état de transition final. Ceci est permis tant qu'aucune des fonctions finales n'est marquées READ_WRITE.)

Si un agrégat accepte le mode d'agrégat par déplacement, cela améliorera l'efficacité du calcul quand l'agrégat est utilisé comme fonction de fenêtrage pour une fenêtre avec un début d'échelle qui se déplace (autrement dit, un mode de début d'échelle autre que UNBOUNDED PRECEDING). Conceptuellement, la fonction de transition ajoute des valeurs en entrée à l'état de l'agrégat quand elles entrent dans la fenêtre à partir du bas, et la fonction de transition inverse les supprime de nouveau quand elles quittent la fenêtre par le haut. Donc, quand les valeurs sont supprimées, elles sont toujours supprimées dans le même ordre qu'elles ont été ajoutées. Quand la fonction de transition inverse est appelée, elle va de ce fait recevoir l'entrée la plus récemment ajoutée, mais pas supprimée. La fonction de transition inverse peut assumer qu'au moins une ligne restera dans l'état courant après avoir supprimé la ligne la plus ancienne. (Quand cela n'est pas le cas, le mécanisme de la fonction de fenêtrage lance une nouvelle agrégation, plutôt que d'utiliser la fonction de transition inverse.)

La fonction de transition pour le mode d'agrégat en déplacement n'est pas autorisée NULL comme nouvelle valeur d'état. Si la fonction de transition inverse renvoie NULL, c'est pris comme une indication que la fonction inverse ne peut pas inverser le calcul d'état pour cette entrée particulière et donc que le calcul d'agrégat sera fait depuis le début à partir du début de l'échelle. Cette convention permet l'utilisation du mode d'agrégat en déplacement dans des situations où il existe certains cas peu courants où il serait difficile d'inverser la valeur d'état courante.

Si aucune implémentation des agrégats en déplacement n'est fournie, l'agrégat peut toujours être utilisé avec des échelles en déplacement mais PostgreSQL devra recalculer l'agrégat complet à partir du débat du déplacement de l'échelle. Notez que si l'agrégat supporte ou non le mode d'agrégat en déplacement, PostgreSQL peut gérer la fin d'une échelle en déplacement sans recalcul ; ceci se fait en continuant d'ajouter de nouvelles valeurs à l'état de l'agrégat. C'est pourquoi l'utilisation d'un agrégat comme fonction de fenêtrage nécessite que la fonction finale soit en lecture seule : elle ne doit pas endommager la valeur d'état de l'agrégat, pour que l'agrégation puisse être continuée même après qu'une valeur de résultat de l'agrégat soit obtenue par un ensemble.

La syntaxe pour des agrégats d'ensemble trié permet d'utiliser VARIADIC pour à la fois le dernier paramètre direct et le dernier paramètre agrégé (WITHIN GROUP). Néanmoins, l'implémentation actuelle restreint l'utilisation de VARIADIC de deux façons. Tout d'abord, les agrégats d'ensemble trié peuvent seulement utiliser VARIADIC "any", et pas les autres types de tableaux variadiques. Ensuite, si le dernier paramètre direct est VARIADIC "any", alors il peut y avoir seulement un paramètre agrégé et il doit aussi être VARIADIC "any". (Dans la représentation utilisée dans les catalogues systèmes, ces deux paramètres sont assemblés en un seul élément VARIADIC "any", car pg_proc ne peut pas représenter des fonctions avec plus d'un argument VARIADIC.) Si l'agrégat est un agrégat d'ensemble hypothétique, les arguments directs qui correspondent au paramètre VARIADIC "any" sont les paramètres hypothétiques. Tous les paramètres précédents représentent des arguments directs supplémentaires qui ne sont pas contraint à correspondre aux arguments agrégés.

Actuellement, les agrégats d'ensemble trié neont pas besoin de supporter le mode d'agrégat en déplacement puisqu'elles ne peuvent pas être utilisées en tant que fonction de fenêtrage.

L'agrégat partiel (y compris parallélisé) n'est pas encore supporté pour les agrégats avec des ensembles de données triés. De plus, il ne sera jamais utilisé pour les appels d'agrégat incluant les clauses DISTINCT ou ORDER BY car ces sémantiques ne peuvent pas être supportées lors d'un agrégat partiel.

Exemples

Voir Section 37.12.

Compatibilité

CREATE AGGREGATE est une extension PostgreSQL. Le standard SQL ne fournit pas de fonctions d'agrégat utilisateur.

Voir aussi