Hlavní navigace

Jemný úvod do PL/pgSQL (3)

Pavel Stěhule

Na závěr našeho miniseriálu o PL/pgSQL se podíváme na návrh funkcí triggerů a na použití PL/pgSQL funkcí s parametry typu tabulka.

Návrh funkcí triggerů v PL/pgSQL

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

Našli jste v článku chybu?

3. 9. 2002 2:42

Václav Dvořák (neregistrovaný)

Nechtěl bych vypadat jako hnidopich, náčelníku, ale tohle si neodpustím, neb je to mé oblíbené téma: "viz" není zkratka (proto se za ním nepíše tečka), nýbrž rozkazovací způsob 2. os. sg. slovesa "vidět" (proto se za ním používá předmět ve čtvrtém pádu - viz koho, co). Správně je tedy "viz klauzuli REFERENCES", nikoliv "viz. klauzule..."

Děkuji za pozornost. :-)



Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

DigiZone.cz: Test Philips 24PFS5231 s Bluetooth repro

Test Philips 24PFS5231 s Bluetooth repro

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Lupa.cz: Insolvenční řízení kvůli cookies? Vítejte v ČR

Insolvenční řízení kvůli cookies? Vítejte v ČR

Lupa.cz: Teletext je „internetem hipsterů“

Teletext je „internetem hipsterů“

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Vitalia.cz: Proč vás každý zubař posílá na dentální hygienu

Proč vás každý zubař posílá na dentální hygienu

120na80.cz: Pánové, pečujte o svoje přirození a prostatu

Pánové, pečujte o svoje přirození a prostatu

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Vitalia.cz: Taky věříte na pravidlo 5 sekund?

Taky věříte na pravidlo 5 sekund?

Vitalia.cz: Pamlsková vyhláška bude platit jen na základkách

Pamlsková vyhláška bude platit jen na základkách

Měšec.cz: Kdy vám stát dá na stěhování 50 000 Kč?

Kdy vám stát dá na stěhování 50 000 Kč?

Vitalia.cz: Když přijdete o oko, přijdete na rok o řidičák

Když přijdete o oko, přijdete na rok o řidičák

Vitalia.cz: Spor o mortadelu: podle Lidlu falšovaná nebyla

Spor o mortadelu: podle Lidlu falšovaná nebyla

Vitalia.cz: Jmenuje se Janina a žije bez cukru

Jmenuje se Janina a žije bez cukru

120na80.cz: Na ucho teplý, nebo studený obklad?

Na ucho teplý, nebo studený obklad?

Vitalia.cz: Paštiky plné masa ho zatím neuživí

Paštiky plné masa ho zatím neuživí

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Vitalia.cz: Znáte „černý detox“? Ani to nezkoušejte

Znáte „černý detox“? Ani to nezkoušejte