Procedury składowane w PostgreSQL/Funkcje agregujące

Polecenie tworzące nową funkcję agregującą ma składnię:

CREATE AGGREGATE nazwa_agregatu(typ_wynikowy) (
        SFUNC = funkcja_przejścia,
        STYPE = typ_stanu_wewnętrznego,
      [ FINALFUNC = funkcja_wynikowa, ]
        INITCOND = wartość_początkowa
);

Do działania potrzebne są jedna lub dwie procedury składowane:

• funkcja przejścia - która parę stan wewnętrzny i aktualną wartość przekształca na nowy stan wewnętrzny;
• funkcja wynikowa [opcjonalna] - która po zakończeniu agregacji zwraca ostateczny wynik na podstawie stanu wewnętrznego.

Agregacja przebiega wg schematu:

DECLARE
    stan    typ_stanu_wewnętrznego := wartość_początkowa;
BEGIN   
    FOR wartość IN zapytanie LOOP
        stan := funkcja_przejścia(stan, wartość);
    END LOOP;

    IF funkcja_wynikowa zdefiniowana THEN
        RETURN funkcja_wynikowa(stan);
    ELSE
        RETURN stan;
    END IF;
END;

Przykład 1

W przykładzie tworzona jest funkcja agregująca, która równocześnie liczy MIN i MAX.

CREATE TYPE min_max_t AS (min integer, max integer);

CREATE OR REPLACE FUNCTION min_max_agg(state min_max_t, x integer, OUT result min_max_t) AS $$
    BEGIN
        IF x > state.max THEN
            result.min := state.min;
            result.max := x;
        ELSIF x < state.min THEN
            result.min := x;
            result.max := state.max;
        ELSE
            result := state;
        END IF;
    END;
$$ LANGUAGE 'plpgsql';

DROP AGGREGATE min_max(integer);
CREATE AGGREGATE min_max(integer) (
    SFUNC = min_max_agg,
    STYPE = min_max_t,
    INITCOND = '(0,0)'
);

Przykład działania:

$ SELECT min_max(x)
  FROM (SELECT (random()*100 - 50)::integer AS x FROM generate_series(1, 10)) t;

 min_max  
----------
 (-10,46)
(1 row)

Przykład 2

Przykład funkcji agregującej z funkcją wynikową, która oblicza rozpiętość wartości, tj. MAX - MIN.

CREATE OR REPLACE FUNCTION min_max_distance(x min_max_t) RETURNS integer AS $$
    BEGIN
        RETURN x.max - x.min;
    END;
$$ LANGUAGE 'plpgsql';

CREATE AGGREGATE distance(integer) (
    SFUNC = min_max_agg,
    STYPE = min_max_t,
    FINALFUNC = min_max_distance,
    INITCOND = '(0,0)'
);

Przykład działania:

$ SELECT distance(x)
  FROM (SELECT (random()*100 - 50)::integer AS x FROM generate_series(1, 10)) t;

 distance  
----------
      56
(1 row)