Jemný úvod do PL/pgSQL (3)

Starší verze PostgreSQL nepodporovaly tzv. kontrolu referenční integrity. Pokud bylo žádoucí (žádoucí je to vždy) referenční integritu zajistit, bylo nutné navrhovat vlastní triggery. Od verze 7.0 PostgreSQL kontrolu referenční integrity zabezpečuje (i když pomocí generovaných triggerů), viz. klauzule REFERENCES. Což však neznamená, že se bez triggerů obejdeme. Použijeme je v případech, kdy je standardní model referenční integrity nedostačující nebo když chceme akce prováděné na jedné tabulce promítnout i do jiných tabulek.

Druhý případ je jednodušší, a proto s ním začnu. Dovolím si malou vsuvku o triggerech. Trigger, česky spoušť, je uložená procedura, kterou RDBMS aktivuje před nebo po provedení příkazů INSERT, UPDATE a DELETE na nějaké tabulce, které předtím určíme trigger. Jako trigger můžeme použít libovolnou PL/pgSQL proceduru bez parametrů vracející hodnotu typu OPAQUE.

Představme si situaci, kdy z nějakého důvodu chceme mít data rozdělená do dvou tabulek. Pracovat však chceme se sjednocením těchto tabulek. Pokud použijeme pohled, vzdáme se možnosti používat referenční integritu. Dalším řešením je používat pomocnou tabulku, která bude obsahovat hodnoty obou zdrojových tabulek.

CREATE TABLE zdroj1 (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE TABLE zdroj2 (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE TABLE cil (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> ''),
  zdroj CHAR(1) NOT NULL CHECK (zdroj = '1' OR zdroj = '2')
);

Předesílám, že jakákoliv funkce PL/pgSQL běží pod implicitní transakcí, tj. selže-li libovolný příkaz v proceduře, pak se veškeré změny v datech provedených funkcí anulují. Na tomto chování je postavena funkce triggeru. Zkusím napřed provést požadovanou funkci na cílové tabulce, pokud příkaz selže, nepodaří se provést jej ani na zdrojové tabulce.

CREATE OR REPLACE FUNCTION trig_build_cil_F() RETURNS OPAQUE AS '
BEGIN
  IF TG_OP = ''DELETE'' THEN
DELETE FROM cil WHERE kod = OLD.kod;
RETURN OLD;
  ELSE
IF TG_OP = ''UPDATE'' THEN
  UPDATE cil SET kod = NEW.kod, popis = NEW.popis WHERE kod = OLD.kod;
  RETURN NEW;
ELSE
  INSERT INTO cil VALUES(NEW.kod, NEW.popis,
CASE TG_RELNAME WHEN 'zdroj1' THEN 1 WHEN 'zdroj2' THEN 2 END);
  RETURN NEW;
END IF;
  END IF;
END;
' LANGUAGE 'plpgsql';

Při provádění PL/pgSQL funkce jako triggeru máme k dispozici několik vestavěných proměnných. TG_OP popisuje příkaz vedoucí k spuštění triggeru, TG_RELNAME nese název tabulky (převedený na malá písmena), na které se trigger spustil, NEW obsahuje řádek s novou verzí hodnot (pouze pro INSERT a UPDATE), OLD obsahuje řádek s původní verzí hodnot (pouze pro DELETE a UPDATE). Pokud procedura vrátí NULL, pak se neprovede změna dat. Trigger řeší pouze přenos dat ze zdrojových tabulek do cílové tabulky. Pokud budeme měnit přímo cílovou tabulku, pak cílová tabulka nebude odpovídat zdrojovým tabulkám. Této nekonzistentnosti lze spolehlivě zabránit až od verze 7.3, kdy uložená procedura může běžet s právy vlastníka. Vlastník bude jediný, kdo bude moci modifikovat cílovou tabulku. Ostatní ji budou moci pouze číst.

Funkci musíme přiřadit triggeru a tabulce příkazem CREATE TRIGGER. V zásadě můžeme vytvořit dva základní typy: ty, které se provádějí před provedením příkazu, vyvolaného triggerem, a ty, které se spouštějí po provedení příkazu.

CREATE TRIGGER t_zdroj1
  BEFORE INSERT OR UPDATE OR DELETE ON zdroj1
  FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F();

CREATE TRIGGER t_zdroj2
  BEFORE INSERT OR UPDATE OR DELETE ON zdroj2
  FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F();

V některých případech chceme zamezit UPDATE některých sloupců tabulek (většinou se jedná o sloupce typu SERIAL, kde UPDATE nemá smysl). Opět existuje doplněk noupdate, který řeší tento problém, ale není defaultně nainstalován. Pokud pokusů o UPDATE nebude mnoho, můžeme bez rozpaků použít trigger a krátkou PL/pgSQL proceduru:

DROP SEQUENCE ciselnik_id_seq;
DROP TABLE ciselnik;

CREATE TABLE ciselnik (
  id SERIAL PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE OR REPLACE FUNCTION trig_static_id() RETURNS OPAQUE AS '
BEGIN
  IF TG_OP = ''UPDATE'' AND NEW.id <> OLD.id THEN
RAISE EXCEPTION ''You can not update PRIMARY KEY column
   on table %'', TG_RELNAME;
  ELSE
RETURN NEW;
  END IF;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER static_id_ciselnik
  BEFORE UPDATE ON ciselnik
  FOR EACH ROW EXECUTE PROCEDURE trig_static_id();

Počínaje verzí 7.3 RDBMS při rušení tabulky zruší i všechny implicitní posloupnosti vytvořené pro sloupce typu SERIAL.

Pomocí triggerů můžeme zajistit integritu n x (1 ku m). Mějme entity maloplošné rezervace, velkoplošné rezervace a památné stromy. Ke každé této entitě chci mít přiřazeno 0 až m dokumentů. Z určitých důvodů chci mít jednoznačný primární klíč přes všechny tabulky chráněných území (tj. entity: maloplošné rezervace (mchu), velkoplošné rezervace (vchu)a památné stromy (pp)). Z praktického hlediska si u entity rezervační knihy eviduji kromě primárního klíče i název tabulky, na kterou se odkazuji (rychlost). Standardní referenční integrita dovoluje se odkazovat pouze na jednu tabulku, tudíž ji nemůžeme použít. Musíme za ni zajistit, aby:

• se nezrušil záznam z tabulek chráněných území, pokud na něj bude existovat odkaz z tabulky dokumentů (tabulky tzv. rezervačních knih).
• do tabulky rezervačníh knih se nesmí přidat záznam s odkazem na neexistující klíč v tabulkách chráněných území.

Příklad 3

V definici tabulek používám operátor implikace, viz. definice vlastních operátorů výše. Následující funkce bude sloužit jako UPDATE, DELETE trigger pro tabulky MCHU, VCHU a PS. V případě, že budu rušit záznam, na který se odkazuji z tabulky rezervačních knih, vyvolá výjimku.

Příklad 4

Použití PL/pgSQL funkcí s parametry typu tabulka

V některých případech potřebujeme spouštět funkci na celý řádek tabulky, např. když potřebujeme nahradit opakující se výraz CASE v příkazu SELECT. Mějme následující tabulky prodejců a prodeje. Tabulka prodeje obsahuje sloupce c1, c2, c3, t2, ot, oec1, oec2, oec3, t1, b1, b2, c050, c150, c300, t1n, t2n, které obsahují počet prodaných kusů v daném tarifu v rámci jednoho prodeje. Tarify jsou stanoveny direktivně a nemění se.

CREATE TABLE prodejci (
  id SERIAL PRIMARY KEY,
  rc VARCHAR(10) NOT NULL UNIQUE CHECK (rc ~ '^[0-9]{9,10}$'),
  jmeno VARCHAR(20) NOT NULL CHECK (jmeno <> ''),
  prijmeni VARCHAR(20) NOT NULL CHECK (prijmeni <> '')
);

CREATE TABLE prodej (
  id SERIAL PRIMARY KEY,
  prodejce int REFERENCES prodejci(id),
  closing_date DATE NOT NULL DEFAULT current_date::DATE,
  c1   integer NULL CHECK(c1 > 0),
  c2   integer NULL CHECK(c2 > 0),
  c3   integer NULL CHECK(c3 > 0),
  t2   integer NULL CHECK(t2 > 0),
  ot   integer NULL CHECK(ot > 0),
  oec1 integer NULL CHECK(oec1 > 0),
  oec2 integer NULL CHECK(oec2 > 0),
  oec3 integer NULL CHECK(oec3 > 0),
  t1   integer NULL CHECK(t1 > 0),
  b1   integer NULL CHECK(b1 > 0),
  b2   integer NULL CHECK(b2 > 0),
  c050 integer NULL CHECK(c050 > 0),
  c150 integer NULL CHECK(c150 > 0),
  c300 integer NULL CHECK(c300 > 0),
  t1n  integer NULL CHECK(t1n > 0),
  t2n  integer NULL CHECK(t2n > 0)
);

Zákazník bude požadovat rozpis prodaných kusů, nikoliv však podle jednotlivých tarifů, ale podle celkového počtu kusů rozděleného do tříd podle objemu na jeden provedený prodej. Třídy jsou:

  0 - 4, 5 - 9, 10 - 19, 20 - 49, nad 50

Z rozpisu pak mohu určit jednak výši prodeje jedním prodejcem, jednak jeho zaměření na určitý segment trhu. Rozpis můžeme realizovat jako výběr s použitím příkazu CASE (níže), nebo jako výběr s PL/pgSQL funkcí, jejímž argumentem je řádek tabulky.

CREATE OR REPLACE FUNCTION isnz(integer) RETURNS integer AS '
  BEGIN
    IF $1 IS NOT NULL THEN
      RETURN $1;
    ELSE
      RETURN 0;
    END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION c_prodej_seg (prodej, int,
   int) RETURNS integer AS '
  DECLARE s INTEGER;
  BEGIN
    s := isnz($1.c1) + isnz($1.c2) + isnz($1.c3) + isnz($1.t2)
      + isnz($1.oec1) + isnz($1.oec2) + isnz($1.oec3) + isnz($1.t1)
      + isnz($1.b1) + isnz($1.b2) + isnz($1.c150) + isnz($1.c300)
      + isnz($1.t1n) + isnz($1.t2n) + isnz($1.ot);
    IF $2 IS NOT NULL THEN
      IF s < $2 THEN RETURN 0; END IF;
    END IF;
    IF $3 IS NOT NULL THEN
      IF s > $3 THEN RETURN 0; END IF;
    END IF;
    RETURN s;
  END;
' LANGUAGE 'plpgsql';

Použití těchto funkcí zásadně zjednoduší návrh. Funkce isnz vrací předanou hodnotu, nebo nulu v případě, že je argument NULL. Funkce c_prodej_seg sečte prodej ve všech tarifech. Pokud je objem prodeje v intervalu určeném druhým a třetím argumentem, vrátí tuto hodnotu, jinak vrací nulu. Všimněte si, že (ač trochu nelogicky) první argument funkce je typu tabulka. Ve funkci samotné však s touto hodnotou pracujeme způsobem, jako kdyby byla tabulka%ROW.

Pro data:

INSERT INTO prodejci (rc, jmeno, prijmeni)
  VALUES ('7307150000','Pavel', 'Stěhule');
INSERT INTO prodejci (rc, jmeno, prijmeni)
  VALUES ('7807150000','Zdeněk', 'Stěhule');

INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,11);
INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (1,50,24);
INSERT INTO prodej (prodejce,t1,c3) VALUES (1,1,1);
INSERT INTO prodej (prodejce,c300)  VALUES (2,10);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,11,2);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,1,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (2,5,12);
INSERT INTO prodej (prodejce,t1,ot) VALUES (2,1,1);
INSERT INTO prodej (prodejce,c300)  VALUES (2,252);

Vrací tento dotaz:

SELECT
  SUM(c_prodej_seg(prodej,NULL, 4)) AS lt5,
  SUM(c_prodej_seg(prodej,5, 9)) AS be5a9,
  SUM(c_prodej_seg(prodej,10, 19)) AS be10a19,
  SUM(c_prodej_seg(prodej,20, 49)) AS be20a49,
  SUM(c_prodej_seg(prodej,50, NULL)) AS ge50,
  jmeno || ' ' || prijmeni as prodejce
from
  prodej join prodejci on prodejce = prodejci.id
group
  by jmeno || ' ' || prijmeni ;

tabulku:

 lt5 | be5a9 | be10a19 | be20a49 | ge50 |    prodejce
-----+-------+---------+---------+------+----------------
   2 |     0 |       0 |      53 |   74 | Pavel Stěhule
   2 |     7 |      40 |       0 |  252 | Zdeněk Stěhule

Ke stejnému výsledku se můžeme dostat jedním příkazem SELECT. Pro jednoduchost budu sčítat pouze tarify c1, c2 a c3.

SELECT
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 0 AND 4
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS le4,
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 5 AND 9
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be5a9,
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 10 AND 19
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be10a19,
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 20 AND 49
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be20a49,
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) >= 50
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS ge50,
  jmeno || ' ' || prijmeni as prodejce
from
  prodej join prodejci on prodejce = prodejci.id
group
  by jmeno || ' ' || prijmeni ;

Parametrů typu tabulka může být více, smysluplné použítí mne však napadá pouze u joinovaných tabulek.

Odkazy:
www.brasileiro­.net/postgres/co­okbook www.zope.org/Mem­bers/pupq/pg_in_ag­gregates developer.pos­tgresql.org/doc­s/postgres/re­lease.html#RE­LEASE-DEVEL