CREATE AGGREGATE — Définir une nouvelle fonction d'agrégat
+CREATE AGGREGATEnom( [mode_arg] [nom_arg]type_donnees_arg[ , ... ] ) ( SFUNC =sfonc, STYPE =type_donnée_état[ , SSPACE =taille_donnée_état] [ , FINALFUNC =ffonc] [ , FINALFUNC_EXTRA ] [ , COMBINEFUNC =combinefunc] [ , SERIALFUNC =serialfunc] [ , DESERIALFUNC =deserialfunc] [ , INITCOND =condition_initiale] [ , MSFUNC =msfonc] [ , MINVFUNC =minvfonc] [ , MSTYPE =type_donnée_état_m] [ , MSSPACE =taille_donnée_état_m] [ , MFINALFUNC =mffonc] [ , MFINALFUNC_EXTRA ] [ , MINITCOND =condition_initiale_m] [ , SORTOP =operateur_tri] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] [ , HYPOTHETICAL ] ) CREATE AGGREGATEnom( [ [mode_arg] [nom_arg]type_donnees_arg[ , ... ] ] ORDER BY [mode_arg] [nom_arg]type_donnees_arg[ , ... ] ) ( SFUNC =sfonc, STYPE =type_donnée_état[ , SSPACE =taille_donnée_état] [ , FINALFUNC =ffonc] [ , FINALFUNC_EXTRA ] [ , INITCOND =condition_initiale] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ) ou l'ancienne syntaxe CREATE AGGREGATEnom( BASETYPE =type_base, SFUNC =sfonc, STYPE =type_donnée_état[ , SSPACE =taille_donnée_état] [ , FINALFUNC =ffonc] [ , FINALFUNC_EXTRA ] [ , COMBINEFUNC =combinefunc] [ , SERIALFUNC =serialfunc] [ , DESERIALFUNC =deserialfunc] [ , SERIALTYPE =serialtype] [ , INITCOND =condition_initiale] [ , MSFUNC =sfunc] [ , MINVFUNC =invfunc] [ , MSTYPE =state_data_type] [ , MSSPACE =taille_donnée_état] [ , MFINALFUNC =ffunc] [ , MFINALFUNC_EXTRA ] [ , MINITCOND =condition_initiale] [ , SORTOP =operateur_tri] )
CREATE AGGREGATE définit une nouvelle fonction d'agrégat.
Quelques fonctions d'agrégat basiques et largement utilisées sont fournies
dans la distribution standard ; elles sont documentées dans le
Section 9.20.
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.
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-interneffonc( é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.10.1. Ceci requiert
de spécifier les paramètres MSFUNC, MINVFUNC,
et MSTYPE et, en option, les paramètres
MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA,
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 le 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 essentiel 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.10.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.
nomLe nom de la fonction d'agrégat à créer (éventuellement qualifié du nom du schéma).
mode_arg
Le mode d'un argument : IN ou VARIADIC.
(Les fonctions d'agrégat n'acceptent pas les arguments OUT.)
Si le mode est omis, la valeur par défaut est IN. Seul
le dernier argument peut être marqué comme VARIADIC.
nom_argLe nom d'un argument. Ceci est seulement utile pour de la documentation. S'il est omis, l'argument n'a pas de nom.
type_données_arg
Un type de donnée en entrée sur lequel opère la fonction d'agrégat.
Pour créer une fonction d'agrégat sans argument, placez *
à la place de la liste des types de données en argument. (la fonction
count(*) en est un bon exemple.)
type_base
Dans l'ancienne syntaxe de CREATE AGGREGATE, le type
de données en entrée est spécifiée par un paramètre
type_base plutôt que d'être écrit à la suite du nom de
l'agrégat. Notez que cette syntaxe autorise seulement un paramètre en
entrée. Pour définir une fonction d'agrégat sans argument avec cette syntaxe, indiquez
seulement un paramètre en entrée. Pour définir une fonction d'agrégat sans argument,
utilisez "ANY" (et non pas *) pour le
type_base. Les agrégats d'ensemble trié ne peuvent
pas être définis avec l'ancienne syntaxe.
sfonc
Le nom de la fonction de transition de l'état à appeler pour chaque ligne
en entrée. Pour une fonction d'agrégat simple avec N arguments, sfonc doit prendre
N+1 arguments, le premier
étant de type type_données_état
et le reste devant correspondre aux types de données en entrée déclarés
pour l'agrégat. La fonction doit renvoyer une valeur de type
type_données_état. Cette
fonction prend la valeur actuelle de l'état et les valeurs actuelles des
données en entrée. Elle renvoit la prochaine valeur de l'état.
Pour les agrégats d'ensemble trié (incluant les ensembles hypothétiques), la fonction de transition d'état reçoit seulement la valeur de l'état actuel et les arguments agrégés, pas les arguments directs.
type_donnée_étatLe type de donnée pour la valeur d'état de l'agrégat.
taille_données_état
La taille moyenne approximative (en octets) de la valeur d'état de
l'agrégat. Si ce paramètre est omis ou s'il vaut zéro, une estimation par
défaut est utilisé en se basant sur
type_données_état. Le planificateur utilise
cette valeur pour estimer la mémoire requise pour une requête d'agrégat
par groupe. Le planificateur considérera l'utilisation d'une agrégation
par hachage pour une telle requête seulement si la table de hachage est
estimée être contenu dans work_mem ; de ce
fait, une grosse valeur pour ce paramètre a tendance à diminuer
l'utilisation des agrégats par hachage.
ffonc
Le nom de la fonction finale à appeler pour traiter le résultat de
l'agrégat une fois que toutes les lignes en entrée ont été parcourues.
Pour un agrégat normal, la fonction prend un seul argument de type type_donnée_état. Le type de retour de
l'agrégat de la fonction est défini comme le type de retour de cette
fonction. Si ffonc n'est pas
spécifiée, alors la valeur d'état finale est utilisée comme résultat de
l'agrégat et le type de retour est type_donnée_état.
Pour les agrégats d'ensemble trié (incluant les ensembles hypothétiques), la fonction finale reçoit non seulement la valeur de l'état final, mais aussi les valeurs de tous les arguments directs.
Si FINALFUNC_EXTRA est indiqué, en plus de la valeur
de l'état final et des arguments directs, la fonction finale reçoit des
valeurs NULL supplémentaires correspondant aux arguments agrégés standards
de l'agrégat. Ceci est principalement utile pour permettre une bonne
résolution du type de données pour le résultat agrégé quand un agrégat
polymorphique est en cours de définition.
combinefunc
La fonction combinefunc
peut être indiquée en option pour permettre à la fonction d'agrégat de
supporter l'agrégation partielle. Si elle est fournie, la fonction
combinefunc doit combiner
deux valeurs state_data_type, chacune contenant le
résultat de l'agrégation sur un certain sous-ensemble des valeurs en
entrée pour produire un nouveau state_data_type qui représente le
résultat de l'agrégation sur les différents ensembles en entrée. Cette
fonction peut être vue comme un sfunc, où, au lieu d'agir sur une ligne
individuelle en entrée et de l'ajouter à l'état de l'agrégat en cours,
elle ajoute un autre état d'agrégat à l'état en cours.
La fonction combinefunc
doit être déclarée comme prenant deux arguments de type state_data_type et renvoyant une valeur
de type state_data_type. En
option, cette fonction pourrait être « strict ». Dans ce cas,
la fonction ne sera pas appelé quand l'un des états en entrée est
null ; l'autre état sera utilisé comme résultat.
Pour les fonctions d'agrégat où state_data_type vaut
internal, la fonction combinefunc ne doit pas être stricte.
Dans ce cas, la fonction combinefunc doit s'assurer que les états
null sont gérés correctement et que l'état à renvoyer est correctement
enregistré dans le contexte mémoire de l'agrégat.
serialfunc
Une fonction d'agrégat dont state_data_type est
internal peut participet à une agrégation en parallèle
seulement si elle a une fonction serialfunc, qui doit sérialiser l'état
d'agrégat en une valeur bytea pour sa transmission à un
autre processus. Cette fonction doit prendre un seul argument de type
internal et renvoyer le bytea. Une fonction
deserialfunc correspondante
est aussi requise.
deserialfunc
Désérialise un état d'agrégat préalablement sérialisé dans son type
state_data_type. Cette
fonction doit prendre deux arguments de type bytea
et internal, et produire un résultat de type internal.
(Note : le second argument, de type internal, n'est
pas utilisé mais est requis pour des raisons de sécurité.)
condition_initiale
La configuration initiale pour la valeur de l'état. Elle doit être une
constante de type chaîne de caractères dans la forme acceptée par le type
de données type_donnée_état.
Si non spécifié, la valeur d'état est initialement positionnée à NULL.
msfonc
Le nom de la fonction de transition d'état à appeler pour chaque ligne en
entrée dans le mode d'agrégat en déplacement. Elle est identique à la
fonction de transition standard, sauf que son premier argument et son
résultat sont de type type_données_état_m,
qui pourrait être différent de type_données_état.
minvfonc
Le nom de la fonction de transition d'état inverse à utiliser dans le mode
d'agrégat en déplacement. Cette fonction a les mêmes types d'argument et
de résultat que msfonc, mais il est utilisé pour
supprimer une valeur de l'état courant de l'agrégat, plutôt que pour y
ajouter une valeur. La fonction de transition inverse doit avoir le même
attribut stricte que la fonction de transaction d'état.
type_données_état_mLe type de données pour la valeur d'état de l'agrégat dans le mode d'agrégat en déplacement.
taille_données_état_m
La taille moyenne approximative (en octets) de la valeur d'état de
l'agrégat. Ceci fonctionne de la même façon que
taille_données_état.
mffonc
Le nom de la fonction finale appelée pour calculer le résultat de
l'agrégat après que toutes les lignes en entrée aient été traversées,
lors de l'utilisation du mode d'agrégat en déplacement. Ceci fonctionne
de la même façon que ffonc, sauf que le type
du premier argument est type_données_état_m
et des arguments supplémentaires sont indiqués en écrivant
MFINALFUNC_EXTRA. Le type en résultat de l'agrégat
déterminé par mffonc ou
mstate_data_type doit correspondre à celui
déterminé par l'implémentation standard de l'agrégat.
condition_initiale_m
La configuration initiale de la valeur d'état lors de l'utilisation du
mode d'agrégat en déplacement. Ceci fonctionne de la même façon que
condition_initiale.
sort_operator
L'opérateur de tri associé pour un agrégat de type MIN ou
MAX. C'est seulement le nom de l'opérateur (éventuellement qualifié
du nom du schéma). L'opérateur est supposé avoir les mêmes
types de données en entrée que l'agrégat (qui doit être un agrégat normal à un seul
argument).
PARALLEL
La signification de PARALLEL SAFE, PARALLEL
RESTRICTED et PARALLEL UNSAFE est la même
que pour CREATE FUNCTION. Un agrégat ne sera pas
considéré pour la parallélisation s'il est marqué PARALLEL
UNSAFE (ce qui est le cas par défaut !) ou
PARALLEL RESTRICTED. Notez que le marquage de
parallélisation des fonctions de support des agrégats ne sont pas
consultés par le planificateur. Ce dernier ne prend en considération que
le marquage de l'agrégat lui-même.
HYPOTHETICAL
Pour les agrégats d'ensembles triés seulement, cette option indique que
les arguments de l'agrégat sont à traiter suivant les prérequis des
agrégats d'ensembles hypothétiques : les derniers arguments directs
doivent correspondre aux types de données des arguments agrégés
(WITHIN GROUP). L'option HYPOTHETICAL
n'a pas d'effet sur le comportement à l'exécution, seulement sur la durée
de résolution de l'analyse des types de données et des collationnements
des arguments de l'agrégat.
Les paramètres de CREATE AGGREGATE peuvent être écrits
dans n'importe quel ordre, pas uniquement dans l'ordre illustré ci-dessus.
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.
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. Il est supposé que la fonction finale n'endommage pas 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.
Voir Section 37.10.
CREATE AGGREGATE est une extension
PostgreSQL. Le standard SQL ne fournit pas de
fonctions d'agrégat utilisateur.