CREATE AGGREGATE — definir una nueva función de agregación
CREATE [ OR REPLACE ] AGGREGATEname( [argmode] [argname]arg_data_type[ , ... ] ) ( SFUNC =sfunc, STYPE =state_data_type[ , SSPACE =state_data_size] [ , FINALFUNC =ffunc] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , COMBINEFUNC =combinefunc] [ , SERIALFUNC =serialfunc] [ , DESERIALFUNC =deserialfunc] [ , INITCOND =initial_condition] [ , MSFUNC =msfunc] [ , MINVFUNC =minvfunc] [ , MSTYPE =mstate_data_type] [ , MSSPACE =mstate_data_size] [ , MFINALFUNC =mffunc] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , MINITCOND =minitial_condition] [ , SORTOP =sort_operator] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ) CREATE [ OR REPLACE ] AGGREGATEname( [ [argmode] [argname]arg_data_type[ , ... ] ] ORDER BY [argmode] [argname]arg_data_type[ , ... ] ) ( SFUNC =sfunc, STYPE =state_data_type[ , SSPACE =state_data_size] [ , FINALFUNC =ffunc] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , INITCOND =initial_condition] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] [ , HYPOTHETICAL ] ) o la sintaxis antigua CREATE [ OR REPLACE ] AGGREGATEname( BASETYPE =base_type, SFUNC =sfunc, STYPE =state_data_type[ , SSPACE =state_data_size] [ , FINALFUNC =ffunc] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , COMBINEFUNC =combinefunc] [ , SERIALFUNC =serialfunc] [ , DESERIALFUNC =deserialfunc] [ , INITCOND =initial_condition] [ , MSFUNC =msfunc] [ , MINVFUNC =minvfunc] [ , MSTYPE =mstate_data_type] [ , MSSPACE =mstate_data_size] [ , MFINALFUNC =mffunc] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , MINITCOND =minitial_condition] [ , SORTOP =sort_operator] )
CREATE AGGREGATE define una nueva función de agregación.
CREATE OR REPLACE AGGREGATE definirá una nueva
función de agregación o reemplazará una definición existente. Algunas funciones
de agregación básicas y de uso común se incluyen en la distribución; están
documentadas en la Section 9.21. Si defines nuevos
tipos o necesitas una función de agregación que no se proporciona, puedes usar
CREATE AGGREGATE para proporcionar las características
deseadas.
Al reemplazar una definición existente, no se pueden cambiar los tipos de argumentos, el tipo de resultado ni el número de argumentos directos. Además, la nueva definición debe ser del mismo tipo (agregación ordinaria, agregación de conjunto ordenado o agregación de conjunto hipotético) que la antigua.
Si se proporciona un nombre de esquema (por ejemplo, CREATE AGGREGATE
myschema.myagg ...), la función de agregación se crea en el esquema
especificado. De lo contrario, se crea en el esquema actual.
Una función de agregación se identifica por su nombre y su(s) tipo(s) de datos de entrada. Dos agregaciones en el mismo esquema pueden tener el mismo nombre si operan sobre diferentes tipos de entrada. El nombre y el(los) tipo(s) de datos de entrada de una agregación también deben ser distintos del nombre y el(los) tipo(s) de datos de entrada de cada función ordinaria en el mismo esquema. Este comportamiento es idéntico a la sobrecarga de nombres de funciones ordinarias (consulta la CREATE FUNCTION).
Una función de agregación simple consta de una o dos funciones ordinarias:
una función de transición de estado sfunc
y una función de cálculo final opcional ffunc.
Estas se utilizan de la siguiente manera:
sfunc( estado-interno, siguientes-valores-de-datos ) ---> siguiente-estado-internoffunc( estado-interno ) ---> valor-de-agregacion
PostgreSQL crea una variable temporal
de tipo de datos stype
para mantener el estado interno actual de la agregación. En cada fila de entrada,
se calculan los valores de los argumentos de la agregación y se invoca la función
de transición de estado con el valor de estado actual y los nuevos valores de
los argumentos para calcular un nuevo valor de estado interno. Después de procesar
todas las filas, se invoca la función final una vez para calcular el valor de
retorno de la agregación. Si no hay una función final, el valor de estado final se
devuelve tal cual.
Una función de agregación puede proporcionar una condición inicial, es decir, un valor
inicial para el valor del estado interno. Esto se especifica y se almacena en la
base de datos como un valor de tipo text, pero debe ser una representación
externa válida de una constante del tipo de datos del valor de estado. Si no se suministra,
el valor de estado comienza como nulo.
Si la función de transición de estado se declara como «strict» (estricta), no se puede
llamar con entradas nulas. Con una función de transición de este tipo, la ejecución de la
agregación se comporta de la siguiente manera: las filas con cualquier valor de entrada
nulo se ignoran (la función no se llama y se mantiene el valor de estado anterior). Si el
valor de estado inicial es nulo, entonces en la primera fila con todos los valores de
entrada no nulos, el valor del primer argumento reemplaza el valor de estado, y la función
de transición se invoca en cada fila posterior con todos los valores de entrada no nulos.
Esto es útil para implementar agregaciones como max. Ten en cuenta que
este comportamiento solo está disponible cuando state_data_type
es el mismo que el primer arg_data_type.
Cuando estos tipos son diferentes, debes proporcionar una condición inicial no nula o usar una
función de transición no estricta.
Si la función de transición de estado no es estricta, se llamará incondicionalmente en cada fila de entrada, y debe manejar las entradas nulas y los valores de estado nulos por sí misma. Esto permite al autor de la agregación tener un control total sobre el manejo de valores nulos por parte de la agregación.
Si la función final se declara como «strict» (estricta), no se llamará cuando el valor de estado
final sea nulo; en su lugar, se devolverá automáticamente un resultado nulo. (Por supuesto,
este es el comportamiento normal de las funciones estrictas). En cualquier caso, la función
final tiene la opción de devolver un valor nulo. Por ejemplo, la función final para
avg devuelve nulo cuando ve que hubo cero filas de entrada.
A veces es útil declarar que la función final tome no solo el valor del estado, sino también
parámetros adicionales que correspondan a los valores de entrada de la agregación. La razón principal
para hacer esto es si la función final es polimórfica y el tipo de datos del valor de estado no
fuera suficiente para determinar el tipo de resultado. Estos parámetros adicionales siempre se
pasan como NULL (por lo que la función final no debe ser estricta cuando se utiliza la opción
FINALFUNC_EXTRA), pero no obstante son parámetros válidos. La función final
podría, por ejemplo, hacer uso de get_fn_expr_argtype para identificar el
tipo de argumento real en la llamada actual.
Una agregación puede admitir opcionalmente el modo de agregación móvil (moving-aggregate mode),
como se describe en la Section 36.12.1. Esto requiere especificar los
parámetros MSFUNC, MINVFUNC y MSTYPE,
y opcionalmente los parámetros MSSPACE, MFINALFUNC,
MFINALFUNC_EXTRA, MFINALFUNC_MODIFY y MINITCOND.
Excepto por MINVFUNC, estos parámetros funcionan como los parámetros correspondientes
de la agregación simple sin M; definen una implementación separada de la agregación
que incluye una función de transición inversa.
La sintaxis con ORDER BY en la lista de parámetros crea un tipo especial de agregación
llamado agregación de conjunto ordenado (ordered-set aggregate); o si se especifica
HYPOTHETICAL, se crea una agregación de conjunto hipotético (hypothetical-set aggregate).
Estas agregaciones operan sobre grupos de valores ordenados de formas que dependen del orden, por lo que la
especificación de un orden de clasificación de entrada es una parte esencial de la llamada. Además, pueden tener
argumentos directos, que son argumentos que se evalúan solo una vez por agregación en lugar
de una vez por fila de entrada. Las agregaciones de conjunto hipotético son una subclase de agregaciones de conjunto
ordenado en la cual se requiere que algunos de los argumentos directos coincidan, en número y tipos de datos, con
las columnas de argumentos agregados. Esto permite que los valores de esos argumentos directos se añadan a la colección
de filas de entrada de la agregación como una fila «hipotética» adicional.
Una agregación puede admitir opcionalmente la agregación parcial (partial aggregation),
como se describe en la Section 36.12.4. Esto requiere especificar el parámetro
COMBINEFUNC. Si el state_data_type es
internal, generalmente también es apropiado proporcionar los parámetros SERIALFUNC y
DESERIALFUNC para que la agregación paralela sea posible. Ten en cuenta que la agregación también
debe estar marcada como PARALLEL SAFE para permitir la agregación paralela.
Las agregaciones que se comportan como MIN o MAX a veces se pueden
optimizar buscando en un índice en lugar de escanear cada fila de entrada. Si esta agregación se puede optimizar
de esta manera, indícalo especificando un operador de ordenamiento (sort operator). El requisito
básico es que la agregación debe devolver el primer elemento en el orden de clasificación inducido por el operador;
en otras palabras:
SELECT agg(col) FROM tab;
debe ser equivalente a:
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
Otras suposiciones son que la agregación ignora las entradas nulas, y que entrega un resultado nulo si y solo si
no hubo entradas no nulas. Normalmente, el operador < de un tipo de datos es el operador de
ordenamiento adecuado para MIN, y > es el operador de ordenamiento adecuado
para MAX. Ten en cuenta que la optimización nunca tendrá efecto a menos que el operador
especificado sea el miembro de estrategia «menor que» o «mayor que» de una clase de operador de índice B-tree.
Para poder crear una función de agregación, debes tener el privilegio USAGE en los tipos de argumentos,
los tipos de estado y el tipo de retorno, así como el privilegio EXECUTE en las funciones de soporte.
nameEl nombre (opcionalmente calificado por esquema) de la función de agregación a crear.
argmode
El modo de un argumento: IN o VARIADIC.
(Las funciones de agregación no admiten argumentos de tipo OUT).
Si se omite, el valor por defecto es IN. Solo el último argumento
puede marcarse como VARIADIC.
argnameEl nombre de un argumento. Actualmente esto solo es útil para fines de documentación. Si se omite, el argumento no tiene nombre.
arg_data_type
Un tipo de datos de entrada sobre el cual opera esta función de agregación. Para crear una
función de agregación con cero argumentos, escribe * en lugar de la lista
de especificaciones de argumentos. (Un ejemplo de tal agregación es count(*)).
base_type
En la sintaxis antigua de CREATE AGGREGATE, el tipo de datos de entrada se especifica
mediante un parámetro basetype en lugar de escribirse al lado del nombre de la
agregación. Ten en cuenta que esta sintaxis permite un solo parámetro de entrada. Para definir una
función de agregación con cero argumentos con esta sintaxis, especifica el basetype
como "ANY" (no *). Las agregaciones de conjunto ordenado no se
pueden definir con la sintaxis antigua.
sfunc
El nombre de la función de transición de estado que se llamará para cada fila de entrada. Para una función de
agregación normal de N argumentos, sfunc
debe tomar N+1 argumentos, siendo el primero de tipo
state_data_type y el resto coincidiendo con los tipos de datos
de entrada declarados de la agregación. La función debe devolver un valor de tipo
state_data_type. Esta función toma el valor de estado actual
y los valores de datos de entrada actuales, y devuelve el siguiente valor de estado.
Para agregaciones de conjunto ordenado (incluidas las de conjunto hipotético), la función de transición de estado recibe únicamente el valor de estado actual y los argumentos agregados, no los argumentos directos. Por lo demás, es igual.
state_data_typeEl tipo de datos para el valor de estado de la agregación.
state_data_size
El tamaño promedio aproximado (en bytes) del valor de estado de la agregación. Si este parámetro se omite
o es cero, se utiliza una estimación por defecto basada en el state_data_type.
El planificador utiliza este valor para estimar la memoria requerida para una consulta de agregación agrupada.
ffunc
El nombre de la función final llamada para calcular el resultado de la agregación después de haber recorrido todas
las filas de entrada. Para una agregación normal, esta función debe tomar un único argumento de tipo
state_data_type. El tipo de datos de retorno de la agregación
se define como el tipo de retorno de esta función. Si no se especifica ffunc,
entonces se utiliza el valor de estado final como resultado de la agregación, y el tipo de retorno es
state_data_type.
Para agregaciones de conjunto ordenado (incluidas las de conjunto hipotético), la función final recibe no solo el valor de estado final, sino también los valores de todos los argumentos directos.
Si se especifica FINALFUNC_EXTRA, entonces además del valor de estado final y cualquier argumento
directo, la función final recibe valores NULL adicionales que corresponden a los argumentos regulares (agregados)
de la agregación. Esto es principalmente útil para permitir la resolución correcta del tipo de resultado de la
agregación cuando se está definiendo una agregación polimórfica.
FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
Esta opción especifica si la función final es una función pura que no modifica sus argumentos.
READ_ONLY indica que no los modifica; los otros dos valores indican que puede cambiar el valor del
estado de transición. Consulta las Notes a continuación para obtener más
detalles. El valor por defecto es READ_ONLY, excepto para las agregaciones de conjunto ordenado,
para las cuales el valor por defecto es READ_WRITE.
combinefunc
La función combinefunc se puede especificar opcionalmente para permitir
que la función de agregación admita la agregación parcial. Si se proporciona, combinefunc
debe combinar dos valores state_data_type, cada uno de los cuales contiene
el resultado de la agregación sobre algún subconjunto de los valores de entrada, para producir un nuevo
state_data_type que represente el resultado de agregar sobre ambos conjuntos
de entradas. Esta función se puede considerar como una sfunc, donde en lugar
de actuar sobre una fila de entrada individual y añadirla al estado de agregación acumulado, añade otro estado de
agregación al estado acumulado.
La función combinefunc debe declararse como que toma dos argumentos de tipo
state_data_type y devuelve un valor de tipo
state_data_type. Opcionalmente, esta función puede ser «strict». En este
caso, la función no se llamará cuando alguno de los estados de entrada sea nulo; el otro estado se tomará como el
resultado correcto.
Para las funciones de agregación cuyo state_data_type es internal,
la función combinefunc no debe ser estricta. En este caso,
combinefunc debe asegurarse de que los estados nulos se manejen correctamente y
de que el estado devuelto se almacene adecuadamente en el contexto de memoria de la agregación.
serialfunc
Una función de agregación cuyo state_data_type es internal puede
participar en la agregación paralela únicamente si tiene una función serialfunc,
la cual debe serializar el estado de la agregación en un valor bytea para su transmisión a otro proceso.
Esta función debe tomar un único argumento de tipo internal y devolver un tipo bytea. También
se requiere una función deserialfunc correspondiente.
deserialfunc
Deserializa un estado de agregación previamente serializado de vuelta a state_data_type.
Esta función debe tomar dos argumentos de tipos bytea e internal, y producir un resultado de tipo
internal. (Nota: el segundo argumento, de tipo internal, no se utiliza, pero es necesario por razones
de seguridad de tipos).
initial_condition
La configuración inicial para el valor del estado. Debe ser una constante de cadena en el formato aceptado para el tipo de datos
state_data_type. Si no se especifica, el valor del estado comienza como nulo.
msfunc
El nombre de la función de transición de estado hacia adelante a llamar para cada fila de entrada en el modo de agregación móvil.
Es exactamente igual que la función de transición regular, excepto que su primer argumento y resultado son de tipo
mstate_data_type, el cual podría ser diferente de state_data_type.
minvfunc
El nombre de la función de transición de estado inversa a utilizar en el modo de agregación móvil. Esta función tiene los mismos
tipos de argumentos y de resultado que msfunc, pero se utiliza para eliminar un valor del estado
de agregación actual, en lugar de añadirlo. La función de transición inversa debe tener el mismo atributo de estrictez
(strictness) que la función de transición de estado hacia adelante.
mstate_data_typeEl tipo de datos para el valor del estado de la agregación cuando se utiliza el modo de agregación móvil.
mstate_data_size
El tamaño promedio aproximado (en bytes) del valor de estado de la agregación cuando se utiliza el modo de agregación móvil.
Funciona de la misma manera que state_data_size.
mffunc
El nombre de la función final llamada para calcular el resultado de la agregación después de haber recorrido todas las filas de
entrada en el modo de agregación móvil. Funciona de la misma manera que ffunc, excepto que el tipo
de su primer argumento es mstate_data_type y los argumentos ficticios adicionales se especifican
escribiendo MFINALFUNC_EXTRA. El tipo de resultado de la agregación determinado por
mffunc o mstate_data_type debe coincidir con el determinado por la
implementación regular de la agregación.
MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
Esta opción es como FINALFUNC_MODIFY, pero describe el comportamiento de la función final de agregación móvil.
minitial_condition
La configuración inicial para el valor del estado cuando se utiliza el modo de agregación móvil. Funciona de la misma manera que
initial_condition.
sort_operator
El operador de ordenamiento asociado para una agregación similar a MIN o MAX.
Es simplemente el nombre de un operador (opcionalmente calificado por esquema). Se asume que el operador tiene los mismos tipos
de datos de entrada que la agregación (que debe ser una agregación normal de un solo argumento).
PARALLEL = { SAFE | RESTRICTED | UNSAFE }
Los significados de PARALLEL SAFE, PARALLEL RESTRICTED y PARALLEL UNSAFE
son los mismos que en CREATE FUNCTION. Una agregación no se
considerará para la paralelización si está marcada como PARALLEL UNSAFE (¡que es el valor por defecto!) o
PARALLEL RESTRICTED. Ten en cuenta que las marcas de seguridad en paralelo de las funciones de soporte de la
agregación no son consultadas por el planificador, únicamente se consulta la marca de la agregación en sí.
HYPOTHETICAL
Únicamente para agregaciones de conjunto ordenado, este indicador especifica que los argumentos de la agregación se van a procesar
de acuerdo con los requisitos para agregaciones de conjunto hipotético: es decir, los últimos argumentos directos deben coincidir con
los tipos de datos de los argumentos agregados (WITHIN GROUP). El indicador HYPOTHETICAL
no tiene efecto en el comportamiento en tiempo de ejecución, únicamente en la resolución en tiempo de análisis de los tipos de datos
y colaciones de los argumentos de la agregación.
Los parámetros de CREATE AGGREGATE se pueden escribir en cualquier orden, no solo en el orden ilustrado anteriormente.
En los parámetros que especifican nombres de funciones de soporte, puedes escribir un nombre de esquema si es necesario, por ejemplo
SFUNC = public.sum. Sin embargo, no escribas los tipos de argumentos allí; los tipos de argumentos de las funciones
de soporte se determinan a partir de otros parámetros.
Normalmente, se espera que las funciones de PostgreSQL sean funciones puras que no modifiquen sus valores de entrada. Sin embargo, una función de transición de agregación, cuando se utiliza en el contexto de una agregación, puede hacer trampas y modificar su argumento de estado de transición in situ (in-place). Esto puede proporcionar beneficios sustanciales de rendimiento en comparación con la realización de una copia nueva del estado de transición cada vez.
Del mismo modo, aunque normalmente se espera que una función final de agregación no modifique sus valores de entrada, a veces no es práctico evitar
modificar el argumento del estado de transición. Dicho comportamiento debe declararse utilizando el parámetro FINALFUNC_MODIFY.
El valor READ_WRITE indica que la función final modifica el estado de transición de formas no especificadas. Este valor impide
el uso de la agregación como una función de ventana (window function), y también impide la fusión de los estados de transición para llamadas de
agregación que compartan los mismos valores de entrada y funciones de transición. El valor SHAREABLE indica que la función de
transición no se puede aplicar después de la función final, pero se pueden realizar múltiples llamadas a la función final sobre el valor de estado
de transición final. Este valor impide el uso de la agregación como una función de ventana, pero permite fusionar estados de transición. (Es decir,
la optimización de interés aquí no es aplicar la misma función final repetidamente, sino aplicar diferentes funciones finales al mismo valor de estado
de transición final. Esto se permite siempre y cuando ninguna de las funciones finales esté marcada como READ_WRITE).
Si una agregación admite el modo de agregación móvil, mejorará la eficiencia del cálculo cuando la agregación se utilice como función de ventana
para una ventana con inicio de marco móvil (es decir, un modo de inicio de marco que no sea UNBOUNDED PRECEDING). Conceptualmente,
la función de transición hacia adelante añade valores de entrada al estado de la agregación cuando entran en el marco de la ventana por la parte inferior,
y la función de transición inversa los elimina de nuevo cuando salen del marco por la parte superior. Por lo tanto, cuando se eliminan valores,
siempre se eliminan en el mismo orden en que fueron añadidos. Cada vez que se invoque la función de transición inversa, recibirá por tanto los
valores de los argumentos añadidos en primer lugar pero que aún no han sido eliminados. La función de transición inversa puede asumir que al menos una
fila permanecerá en el estado actual después de eliminar la fila más antigua. (Cuando este no sea el caso, el mecanismo de la función de ventana simplemente
inicia una nueva agregación, en lugar de usar la función de transición inversa).
La función de transición hacia adelante para el modo de agregación móvil no puede devolver NULL como el nuevo valor de estado. Si la función de transición inversa devuelve NULL, esto se toma como una indicación de que la función inversa no puede revertir el cálculo del estado para esta entrada en particular, por lo que el cálculo de la agregación se volverá a hacer desde cero para la posición de inicio del marco actual. Esta convención permite utilizar el modo de agregación móvil en situaciones donde hay algunos casos poco frecuentes que no es práctico revertir del valor del estado acumulado.
Si no se proporciona ninguna implementación de agregación móvil, la agregación se puede seguir utilizando con marcos móviles, pero PostgreSQL volverá a calcular toda la agregación cada vez que se mueva el inicio del marco. Ten en cuenta que, independientemente de si la agregación admite o no el modo de agregación móvil, PostgreSQL puede manejar un final de marco móvil sin volver a calcular; esto se hace continuando con la adición de nuevos valores al estado de la agregación. Es por ello que el uso de una agregación como función de ventana requiere que la función final sea de solo lectura: no debe dañar el valor del estado de la agregación, de modo que la agregación pueda continuar incluso después de haber obtenido un valor de resultado de la agregación para un conjunto de límites de marco.
La sintaxis para agregaciones de conjunto ordenado permite especificar VARIADIC tanto para el último parámetro directo como para el
último parámetro agregado (WITHIN GROUP). Sin embargo, la implementación actual restringe el uso de VARIADIC de dos
maneras. En primer lugar, las agregaciones de conjunto ordenado únicamente pueden utilizar VARIADIC "any", no otros tipos de matrices variádicas.
En segundo lugar, si el último parámetro directo es VARIADIC "any", únicamente puede haber un parámetro agregado y también debe ser
VARIADIC "any". (En la representación utilizada en los catálogos del sistema, estos dos parámetros se fusionan en un único elemento
VARIADIC "any", ya que pg_proc no puede representar funciones con más de un parámetro VARIADIC).
Si la agregación es una agregación de conjunto hipotético, los argumentos directos que coinciden con el parámetro VARIADIC "any" son los
hipotéticos; cualquier parámetro anterior representa argumentos directos adicionales que no están obligados a coincidir con los argumentos agregados.
Actualmente, las agregaciones de conjunto ordenado no necesitan admitir el modo de agregación móvil, ya que no se pueden utilizar como funciones de ventana.
La agregación parcial (incluida la paralela) no es compatible actualmente con las agregaciones de conjunto ordenado. Además, nunca se utilizará para llamadas
de agregación que incluyan cláusulas DISTINCT u ORDER BY, ya que esa semántica no se puede admitir durante la agregación parcial.
Consulta la Section 36.12.
CREATE AGGREGATE es una extensión del lenguaje de PostgreSQL.
El estándar SQL no contempla funciones de agregación definidas por el usuario.