Descrição

O comando CREATE AGGREGATE define uma nova função de agregação. O comando CREATE OR REPLACE AGGREGATE define uma nova função de agregação, ou substitui a definição existente. Algumas funções de agregação básicas e comumente usadas estão incluídas na distribuição do PostgreSQL; elas estão documentados na Funções de agregação. Se forem criados tipos de dados, ou houver necessidade de uma função de agregação ainda não existente, o comando CREATE AGGREGATE poderá ser usado para fornecer os recursos desejados.

Ao substituir uma definição existente, os tipos de dados dos argumentos, o tipo de dados do resultado, e o número de argumentos diretos não podem ser modificados. Além disso, a nova definição deve ser do mesmo tipo que a antiga (agregação comum, agregação de conjunto ordenado, ou agregação de conjunto hipotético).

Se for fornecido um nome de esquema (por exemplo, CREATE AGGREGATE meu_esquema.minha_agregação ...), a função de agregação será criada no esquema especificado. Caso contrário, será criada no esquema corrente.

Uma função de agregação é identificada por seu nome e tipo(s) de dados de entrada. Duas agregações no mesmo esquema podem ter o mesmo nome, se operarem em diferentes tipos de dados de entrada. O nome e os tipos de dados de entrada de uma agregação também devem ser distintos do nome e dos tipos de dados de entrada de cada função comum no mesmo esquema. Este comportamento é idêntico à sobrecarga de nomes de funções comuns (veja CREATE FUNCTION).

Uma função de agregação simples é feita de uma ou duas funções ordinárias: uma função_de_transição_de_estado, e uma função opcional de cálculo final função_final. Elas são usadas da seguinte forma:

função_de_transição_de_estado( estado-interno, próximo_valores_de_dados ) ⟶ próximo-estado-interno
função_final( estado-interno ) ⟶ valor-agregado

O PostgreSQL cria uma variável temporária do tipo de dados stype para manter o estado interno corrente da agregação. A cada linha de entrada, o valor do argumento agregado é calculado, e a função de transição de estado é chamada com o valor de estado corrente e o valor do argumento para calcular um novo valor de estado interno. Após processar todas as linhas, a função final é chamada uma vez para calcular o valor retornado pela agregação. Se não houver função final, o valor de estado final será retornado como está.

Uma função de agregação pode fornecer uma condição inicial, ou seja, um valor inicial para o valor de estado interno. Este valor é especificado e armazenado no banco de dados como um valor do tipo de dados text, mas deve ter uma representação externa válida de uma constante do tipo de dados do valor de estado. Se não for fornecido, o valor do estado começa com nulo.

Se a função de transição de estado for declarada como “strict”, ela não poderá ser chamada com entradas nulas. Com uma função de transição como esta, a execução da agregação se comporta da seguinte maneira. As linhas com quaisquer valores de entrada nulos são ignoradas (a função não é chamada, e o valor do estado anterior é retido). Se o valor do estado inicial for nulo, na primeira linha com todos os valores de entrada não nulos, o primeiro valor de argumento substituirá o valor de estado, e a função de transição será chamada a cada linha subsequente com todos os valores de entrada não nulos. Serve para implementar agregações como max. Note que este comportamento só está disponível quando o tipo_de_dados_de_estado é igual ao primeiro tipo_de_dados_do_argumento. Quando estes tipos de dados são diferentes, deve ser fornecida uma condição inicial não nula, ou usar uma função de transição não estrita.

Se a função de transição de estado não for estrita, ela será chamada incondicionalmente a cada linha de entrada, devendo lidar com entradas nulas e valores de estado nulos por si mesma. Isto permite que o autor da agregação tenha controle total sobre o tratamento de valores nulos da agregação.

Se a função final for declarada como “strict”, ela não será chamada quando o valor de estado final for nulo; em vez disso, um resultado nulo será retornado automaticamente. (Claro que este é apenas o comportamento normal de funções estritas.) De todo modo, a função final tem a opção de retornar o valor nulo. Por exemplo, a função final para avg retorna nulo quando vê que houve zero linhas de entrada.

Às vezes, é útil declarar a função final como tendo não apenas o valor de estado, mas também parâmetros extras correspondentes aos valores de entrada da agregação. A principal razão para fazer isto é quando a função final é polimórfica, e o tipo de dados do valor de estado é inadequado para definir o tipo de dados do resultado. Estes parâmetros extras são sempre passados como nulo (por isto a função final não deve ser estrita quando a opção FINALFUNC_EXTRA é usada), mas mesmo assim são parâmetros válidos. A função final pode, por exemplo, usar a função get_fn_expr_argtype para identificar o tipo de dados do argumento real na chamada corrente.

Uma agregação pode, opcionalmente, dar suporte ao modo de agregação móvel, conforme descrito na Seção 36.12.1. Isto requer especificar os parâmetros MSFUNC, MINVFUNC, e MSTYPE, e, opcionalmente, os parâmetros MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY, e MINITCOND. Exceto para MINVFUNC, estes parâmetros funcionam como os parâmetros de agregação simples correspondentes sem o M; eles definem uma implementação separada da agregação que inclui uma função de transição inversa.

A sintaxe com ORDER BY na lista de parâmetros cria um tipo especial de agregação chamada agregação de conjunto ordenado; ou se for especificado HYPOTHETICAL, então será criada uma agregação de conjunto hipotético. Estas agregações operam sobre grupos de valores classificados de maneiras dependentes da ordem, de modo que a especificação de uma ordem de classificação de entrada é parte essencial da chamada. Além disso, podem ter argumentos diretos, que são argumentos avaliados apenas uma vez por agregação, em vez de uma vez por linha de entrada. As agregações de conjunto hipotético são uma subclasse das agregações de conjunto ordenado, em que alguns dos argumentos diretos são necessários para corresponder, em número e tipos de dados, às colunas de argumentos agregados. Isto permite que os valores desses argumentos diretos sejam adicionados à coleção de linhas de entrada agregada como uma linha “hipotética” adicional.

Uma agregação pode, opcionalmente, dar suporte a agregação parcial, conforme descrito na Seção 36.12.4. Isto requer a especificação do parâmetro COMBINEFUNC. Se o tipo_de_dados_de_estado for internal, geralmente também é apropriado fornecer os parâmetros SERIALFUNC e DESERIALFUNC para que a agregação paralela seja possível. Note que a agregação também deve ser marcada como PARALLEL SAFE para possibilitar a agregação paralela.

As agregações que se comportam como MIN ou MAX podem, às vezes, ser otimizadas examinando um índice em vez de varrer cada linha de entrada. Se esta agregação puder ser otimizada, indique especificando um operador de classificação. O requisito básico é que a agregação deve produzir o primeiro elemento na ordem de classificação induzida pelo operador; em outras palavras:

SELECT agg(col) FROM tab;

deve ser equivalente a:

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

Outras suposições são de que a agregação ignora entradas nulas, e que entrega um resultado nulo se, e somente se, não houver entradas não nulas. Normalmente, o operador < de um tipo de dados é o operador de classificação adequado para MIN, e > é o operador de classificação adequado para MAX. Note que a otimização nunca terá efeito, a menos que o operador especificado seja o membro da estratégia “menor que” ou “maior que” de uma classe de operador de índice de árvore-B.

Para poder criar uma função de agregação, deve-se ter o privilégio USAGE nos tipos de dados dos argumentos, no(s) tipo(s) de dados de estado, e no tipo de dados do retorno, bem como o privilégio EXECUTE nas funções de suporte.

Parâmetros

Os parâmetros do comando CREATE AGGREGATE podem ser escritos em qualquer ordem, e não apenas na ordem mostrada acima.

Notas

Nos parâmetros que especificam nomes de função de suporte, pode ser escrito um nome de esquema, se necessário, por exemplo SFUNC = public.sum. No entanto, não se deve escrever tipos de dados de argumento aí — os tipos de dados dos argumentos das funções de suporte são determinados a partir de outros parâmetros.

Normalmente, espera-se que as funções do PostgreSQL sejam funções verdadeiras que não modificam seus valores de entrada. Entretanto, uma função de transição da agregação, quando usada no contexto de uma agregação, pode trapacear e modificar seu argumento de estado de transição no local. Isto pode fornecer benefícios substanciais de desempenho em comparação com a criação de uma nova cópia do estado de transição a cada vez.

Da mesma forma, embora normalmente se espere que uma função final de agregação não modifique seus valores de entrada, às vezes é impraticável evitar a modificação do argumento de estado de transição. Este comportamento deve ser declarado usando o parâmetro FINALFUNC_MODIFY. O valor READ_WRITE indica que a função final modifica o estado de transição de maneiras não especificadas. Este valor impede o uso da agregação como função de janela, e também impede a mesclagem de estados de transição para chamadas de agregação que compartilham os mesmos valores de entrada e funções de transição. O valor SHAREABLE indica que a função de transição não pode ser aplicada após a função final, mas várias chamadas da função final podem ser executadas no valor de estado de transição final. Este valor impede o uso da agregação como função de janela, mas permite a mesclagem de estados de transição. (Ou seja, a otimização de interesse aqui não é aplicar a mesma função final repetidamente, mas aplicar diferentes funções finais ao mesmo valor final de estado de transição. Isto é permitido desde que nenhuma das funções finais esteja marcada como READ_WRITE.)

Se uma agregação suportar o modo de agregação móvel, ela melhorará a eficiência do cálculo quando a agregação for usada como função de janela para uma janela com início de quadro móvel (ou seja, um modo de início de quadro diferente de UNBOUNDED PRECEDING). Conceitualmente, a função de transição adiante adiciona valores de entrada ao estado da agregação quando eles entram no quadro da janela pela parte inferior, e a função de transição inversa os remove novamente quando eles saem do quadro pela parte superior. Assim, quando os valores são removidos, eles sempre são removidos na mesma ordem em que foram adicionados. Sempre que a função de transição inversa for chamada, ela receberá o(s) primeiro(s) valor(es) de argumento(s) adicionado(s), mas ainda não removido(s). A função de transição inversa pode assumir que pelo menos uma linha permanecerá no estado corrente após remover a linha mais antiga. (Quando não for este o caso, o mecanismo de função de janela simplesmente inicia uma nova agregação, em vez de usar a função de transição inversa.)

A função de transição adiante para o modo de agregação móvel não pode retornar nulo como o novo valor de estado. Se a função de transição inversa retornar nulo, isto é considerado uma indicação de que a função inversa não pode reverter o cálculo do estado para esta entrada específica, portanto, o cálculo da agregação será refeito do zero para a posição inicial do quadro corrente. Esta convenção permite que o modo de agregação móvel seja usado em situações onde há alguns casos pouco frequentes que são impraticáveis para reverter o valor do estado em andamento.

Se nenhuma implementação de agregação móvel for fornecida, a agregação ainda poderá ser usada com quadros móveis, mas o PostgreSQL recalculará toda a agregação sempre que o início do quadro se mover. Note que, independentemente da agregação dar suporte ou não ao modo de agregação móvel, o PostgreSQL pode lidar com o final de um quadro móvel sem recálculo; isto é feito continuando a adicionar novos valores ao estado da agregação. É por isto que o uso de uma agregação como função de janela requer que a função final seja de somente leitura: ela não deve danificar o valor de estado da agregação, para que a agregação possa continuar mesmo depois que o valor do resultado agregado for obtido para um conjunto de limites de quadro.

A sintaxe para agregações de conjuntos ordenados permite que seja especificado VARIADIC tanto para o último parâmetro direto, quanto para o último parâmetro agregado (WITHIN GROUP). Entretanto, a implementação corrente restringe o uso de VARIADIC de duas maneiras. Primeiro, agregações de conjuntos ordenados só podem usar VARIADIC "any", e não outros tipos de matrizes mutáveis. Em segundo lugar, se o último parâmetro direto for VARIADIC "any", então pode haver apenas um parâmetro agregado, e também deve ser VARIADIC "any". (Na representação usada nos catálogos do sistema, estes dois parâmetros são mesclados em um único item VARIADIC "any", porque pg_proc não pode representar funções com mais de um parâmetro VARIADIC.) Se a agregação for uma agregação de conjunto hipotético, os argumentos diretos que correspondem ao parâmetro VARIADIC "any" são os hipotéticos; quaisquer parâmetros anteriores representam argumentos diretos adicionais que não são restritos para corresponder aos argumentos agregados.

Neste momento, as agregações de conjunto ordenado não precisam dar suporte ao modo de agregação móvel, porque não podem ser usadas como funções de janela.

Também neste momento, a agregação parcial (incluindo a paralela) não tem suporte para agregações de conjunto ordenado. Além disso, nunca será usada para chamadas de agregação que incluam cláusulas DISTINCT ou ORDER BY, porque estas semânticas não podem ter suporte durante a agregação parcial.

Exemplos

Veja Seção 36.12.

Conformidade

O comando CREATE AGGREGATE é uma extensão de linguagem do PostgreSQL. O padrão SQL não contempla funções de agregação definidas pelo usuário.

Veja também