 COPY
|
 
|
CREATE CAST
|
CREATE AGGREGATE — Définir une nouvelle fonction d'agrégat
+CREATE AGGREGATE nom ( [ 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 ]
[ , 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 ]
)
CREATE AGGREGATE nom ( [ [ 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 ]
[ , HYPOTHETICAL ]
)
ou l'ancienne syntaxe
CREATE AGGREGATE nom (
BASETYPE = type_base,
SFUNC = sfonc,
STYPE = type_donnée_état
[ , SSPACE = taille_donnée_état ]
[ , FINALFUNC = ffonc ]
[ , FINALFUNC_EXTRA ]
[ , 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, « Fonctions d'agrégat ». 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(7)).
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éeet 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 35.10.1, « Mode d'agrégat en déplacement ». Ceci requiert de spécifier les paramètres MSFUNC, MINVFUNC, et MSTYPE et, en option, les paramètres MSPACE, 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 ».
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 la fonction de transition et la fonction finale.
Le nom de la fonction d'agrégat à créer (éventuellement qualifié du nom du schéma).
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.
Le nom d'un argument. Ceci est seulement utile pour de la documentation. S'il est omis, l'argument n'a pas de nom.
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.)
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 only one input parameter. To define a zero-argument aggregate function, "ANY" (et non pas *) pour le type_base. Les agrégats d'ensemble trié ne peuvent pas être définis avec l'ancienne syntaxe.
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.
Le type de donnée pour la valeur d'état de l'agrégat.
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.
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.
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.
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.
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.
Le type de données pour la valeur d'état de l'agrégat dans le mode d'agrégat en déplacement.
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.
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.
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.
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).
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.