Dryákova abeceda - (A)GGREGATE

AGGREGATE, použití vlastní agregační funkce

Kromě zabudovaných agregačních funkcí, jako například SUM, AVG občas potřebujeme nějaký nástroj, který umožní příkazem SELECT vybrat na jednom řádku data, definovaná pomocí vztahu 1:N. Jedna z možností, jak to udělat, je použití vlastních agregačních funkcí. Vše je vysvětleno na následujícím příkladu.

Tabulka ifxserver obsahuje informaci o Informix serveru – tedy o tom, co je definováno pomocí parametru DBSERVERNAME v onconfigu. Takto tabulka vypadá:

create table ifxserver
  (
    hostname char(30) not null ,
    onconfig varchar(64) not null ,
    informixserver varchar(64) not null ,
    nettype char(20),
    port integer,
    primary key (hostname,onconfig,informixserver),
    foreign key (hostname,onconfig) references informix_servers
  );

Vyjádřeno slovy, k jedné databázové instanci, která je jednoznačně určena pomocí atributů hostname+onconfig může existovat mnoho Informix serverů, identifikovaných atributem informixserver. Zde je ukázka dat pro konkrétní instanci:

Představme si, že potřebujeme získat výpis všech Informix serverů k instanci na jednom řádku. Tedy něco jako:

hostname  arrow1.apis.dhl.com
onconfig  onconfig.exb_gbl
srvlist   exb_gbl_srv,exb_gbl_shm,exb_sinfo_tcp2

Pokud nechceme podobný výpis programovat v aplikaci, je možné použít příkaz

SELECT hostname,onconfig, group_concat(informixserver) srvlist FROM ifxserver
WHERE hostname = "arrow1.apis.dhl.com" AND onconfig = "onconfig.exb_gbl" GROUP BY hostname,onconfig;

group_concat je vlastní agregační funkce, která se vytvoří následujícím způsobem:

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || ',' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);