31.10. Agregações definidas pelo usuário

PostgreSQL 14.5: Agregações definidas pelo usuário

No PostgreSQL as funções de agregação são expressas em termos de valor de estado e funções de transição de estado, ou seja, uma agregação opera utilizando um valor de estado que é atualizado à medida que cada linha de entrada sucessiva é processada. Para definir uma nova função de agregação deve ser selecionado um tipo de dado para o valor do estado, um valor inicial para o estado, e uma função de transição de estado. A função de transição de estado é apenas uma função comum, que também poderia ser utilizada fora do contexto da agregação. Também pode ser especificada uma função final, para o caso do resultado desejado para a agregação ser diferente do dado mantido no valor de estado.

Portanto, além dos tipos de dado do argumento e do resultado vistos pelo usuário, existe também um tipo de dado para o valor de estado interno que pode ser diferente tanto do tipo de dado do argumento, quanto do tipo de dado do resultado.

Quando se define uma agregação que não utiliza uma função final, se tem uma agregação que processa uma função totalizadora dos valores da coluna para cada linha. sum é um exemplo deste tipo de agregação. sum começa em zero e sempre adiciona o valor da linha corrente ao total sendo calculado. Por exemplo, se for desejado desenvolver uma agregação sum que trabalhe com tipo de dado para números complexos, somente será necessário adicionar uma função para este tipo de dado. A definição da agregação poderia ser:

CREATE AGGREGATE complex_sum (
    sfunc = complex_add,
    basetype = complex,
    stype = complex,
    initcond = '(0,0)'
);

SELECT complex_sum(a) FROM test_complex;

 complex_sum
-------------
 (34,53.9)

(Na prática apenas chamamos a agregação de sum e confiamos que o PostgreSQL descubra que tipo de soma deve ser aplicada a uma coluna do tipo complex)

A definição de sum acima retorna zero, a condição de estado inicial, se não houver nenhum valor de entrada diferente de nulo. Pode ser que neste caso se prefira retornar nulo em vez de zero — o padrão SQL espera que a agregação sum se comporte desta maneira. Isto pode ser feito simplesmente omitindo a linha initcond para que a condição de estado inicial seja nula. Normalmente isto significaria que sfunc precisaria verificar uma entrada tendo nulo na condição de estado, mas para sum, e algumas outras agregações simples como max e min, basta atribuir o primeiro valor de entrada não nulo à variável de estado, e depois começar a aplicar a função de transição a partir do segundo valor de entrada não nulo. O PostgreSQL faz isto automaticamente quando a condição inicial é nula, e a função de transição está marcada como "strict" (ou seja, não é chamada para entradas nulas).

Outro ponto do comportamento padrão da função de transição "strict" a ser ressaltado, é que o valor de estado anterior permanece inalterado quando é encontrado um valor de entrada nulo. Portanto, os valores nulos são ignorados. Se for desejado um comportamento diferente para os valores nulos, a função de transição não deve ser definida como estrita, e deve ser codificada testando as entradas nulas e fazendo o que for necessário.

Um exemplo mais complexo de agregação é avg (média). Requer dois elementos de estado de execução: a soma das entradas e o contador do número de entradas. O resultado final é obtido pela divisão destas quantidades. Usualmente a média é implementada utilizando uma matriz com dois elementos para o valor de estado. Por exemplo, a implementação interna de avg(float8) se parece com:

CREATE AGGREGATE avg (
    sfunc = float8_accum,
    basetype = float8,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0}'
);

As funções de agregação podem utilizar funções de transição de estado e funções finais polimórficas e, portanto, a mesma função pode ser utilizada para implementar várias agregações. Para obter informações sobre funções polimórficas deve ser consultada a Seção 31.2.5. Indo um passo adiante, a própria função de agregação pode ser especificada com um tipo base e um tipo de estado polimórficos, permitindo que uma única definição de agregação sirva para vários tipos de dado de entrada. Abaixo está um exemplo de agregação polimórfica:

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

Neste caso, o tipo de dado real do estado, para qualquer chamada à agregação, é o tipo de dado da matriz que possui o tipo de dado real de entrada como seus elementos.

Abaixo estão mostradas as saídas utilizando dois tipos de dado diferentes como argumentos:

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
    GROUP BY attrelid;

 attrelid |                                 array_accum
----------+-----------------------------------------------------------------------------
 pg_user  | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig}
(1 linha)

SELECT attrelid::regclass, array_accum(atttypid)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
    GROUP BY attrelid;

 attrelid |         array_accum
----------+------------------------------
 pg_user  | {19,23,16,16,16,25,702,1009}
(1 linha)

Para obter mais detalhes deve ser visto o comando CREATE AGGREGATE.

CREATE TABLE tbl_pessoas (
    id          SERIAL,
    nome        TEXT,
    sobrenome   TEXT,
    sexo        CHAR(1),
    altura_cm   INT
);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Ana','Maria','F',175);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Manoel','Pacheco','M',168);
INSERT INTO tbl_pessoas VALUES (DEFAULT,NULL,'Barbosa',NULL,NULL);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Manoel','Oliveira','M',181);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Ana','Fraga','F',165);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Maria','Pereira','F',164);
INSERT INTO tbl_pessoas VALUES (DEFAULT,'Manoel','Silva','M',153);
\pset null '(nulo)'
\pset border 2
\pset title 'Tabela tbl_pessoas'
SELECT * FROM tbl_pessoas;
               Tabela tbl_pessoas
+----+--------+-----------+--------+-----------+
| id |  nome  | sobrenome |  sexo  | altura_cm |
+----+--------+-----------+--------+-----------+
|  1 | Ana    | Maria     | F      |       175 |
|  2 | Manoel | Pacheco   | M      |       168 |
|  3 | (nulo) | Barbosa   | (nulo) |    (nulo) |
|  4 | Manoel | Oliveira  | M      |       181 |
|  5 | Ana    | Fraga     | F      |       165 |
|  6 | Maria  | Pereira   | F      |       164 |
|  7 | Manoel | Silva     | M      |       153 |
+----+--------+-----------+--------+-----------+
(7 linhas)

\set DateStyle 'ISO';
CREATE OR REPLACE FUNCTION fun_estado_agrega_texto (text[], text) RETURNS text[] AS $$
    SELECT array_append($1, $2);
$$ LANGUAGE SQL STRICT;
CREATE OR REPLACE FUNCTION fun_final_agrega_texto (text[]) RETURNS text AS $$
    SELECT array_to_string($1, ';');
$$ LANGUAGE SQL STRICT;
CREATE AGGREGATE agrega_texto
(
    BASETYPE=text,
    SFUNC = fun_estado_agrega_texto,
    STYPE = text[],
    FINALFUNC = fun_final_agrega_texto,
    INITCOND = '{}'
);
\pset null '(nulo)'
\pset border 2
\pset title 'Tabela tbl_pessoas agrupada por sexo'
SELECT sexo, agrega_texto(nome || ' ' || sobrenome) AS pessoas
FROM tbl_pessoas
GROUP BY sexo;

# psql -U teste -f teste.sql -o teste.out -q teste
# cat teste.out
          Tabela tbl_pessoas agrupada por sexo
+--------+---------------------------------------------+
|  sexo  |                   pessoas                   |
+--------+---------------------------------------------+
| (nulo) |                                             |
| M      | Manoel Pacheco;Manoel Oliveira;Manoel Silva |
| F      | Ana Maria;Ana Fraga;Maria Pereira           |
+--------+---------------------------------------------+
(3 linhas)

\set DateStyle 'ISO';
CREATE OR REPLACE FUNCTION fun_estado_agrega_histograma (text) RETURNS text AS $$
    SELECT $1 || '*';
$$ LANGUAGE SQL STRICT;
CREATE AGGREGATE agrega_histograma
(
    BASETYPE = "ANY",
    SFUNC = fun_estado_agrega_histograma,
    STYPE = text,
    INITCOND = ''
);
\pset null '(nulo)'
\pset border 2
\pset title 'Histograma dos nomes'
SELECT nome, count(*) AS qtd, agrega_histograma(*) AS histograma
FROM tbl_pessoas
GROUP BY nome
ORDER BY 2,1;

# psql -U teste -f teste_histograma.sql -o teste_histograma.out -q teste
# cat teste_histograma.out
    Histograma dos nomes
+--------+-----+------------+
|  nome  | qtd | histograma |
+--------+-----+------------+
| Maria  |   1 | *          |
| (nulo) |   1 | *          |
| Ana    |   2 | **         |
| Manoel |   3 | ***        |
+--------+-----+------------+
(4 linhas)

CREATE OR REPLACE FUNCTION fun_estado_lista_inteiros (int[], int) RETURNS int[] AS $$
    SELECT array_append($1, $2);
$$ LANGUAGE SQL STRICT;
CREATE OR REPLACE FUNCTION fun_final_lista_inteiros (int[]) RETURNS text AS $$
    SELECT array_to_string($1, ';');
$$ LANGUAGE SQL STRICT;
CREATE AGGREGATE lista_inteiros
(
    BASETYPE = int,
    SFUNC = fun_estado_lista_inteiros,
    STYPE = int[],
    FINALFUNC = fun_final_lista_inteiros,
    INITCOND = '{}'
);
\pset null '(nulo)'
\pset border 2
\pset title 'Função lista_inteiros()'
SELECT sexo, lista_inteiros(altura_cm) AS alturas
FROM tbl_pessoas
GROUP BY sexo;
\pset title 'Função array_accum()'
SELECT sexo, array_accum(altura_cm) AS alturas
FROM tbl_pessoas
GROUP BY sexo;

# psql -h cmrj_bd -U teste -f teste_lista_inteiros.sql -o teste_lista_inteiros.out -q teste
# cat teste_lista_inteiros.out
 Função lista_inteiros()
+--------+-------------+
|  sexo  |   alturas   |
+--------+-------------+
| (nulo) |             |
| M      | 168;181;153 |
| F      | 175;165;164 |
+--------+-------------+
(3 linhas)

   Função array_accum()
+--------+---------------+
|  sexo  |    alturas    |
+--------+---------------+
| (nulo) | {NULL}        |
| M      | {168,181,153} |
| F      | {175,165,164} |
+--------+---------------+
(3 linhas)