Pokročilé PL/pgSQL

Bleskovka.
Tvorba rôznych typov triggrov.
Vyvolanie a ošetrenie chybových stavov.

V úvode tohto cvičenia Vás čaká bleskovka na zopakovanie písania selectov. Bleskovka pozostáva z 5 úloh. Dnešné cvičenie je následne venované trigger-om (hrubo preložené ako spúšťač). Trigger v PostgreSQL je funkcia, ktorá je vyvolaná vždy ak dôjde k udalosti ako insert, uptade, delete alebo truncate. Je to špeciálna funkcia asociovaná s tabuľkou. Hlavný rozdiel medzi funkciou a triggerom je, že trigger sa automatický vyvolá pri danej situácií.

Pre vytvorenie triggera je potrebné najprv vytvoriť "trigger funkciu". Takáto funkcia sa vytvára ako regulárna funkciu, avšak nesmie brať žiadne parametre a musí vrátiť hodnotu typu TRIGGER. Zjednodušená syntax trigger funkcie:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

Trigger funkciu je možne vytvoriť v akomkoľvek jazyku podporovanom v PostgreSQL. My pokračujeme v PL/pgSQL.

Samotný trigger je možné vytvoriť pomocou príkazu CREATE TRIGGER. Zjednodušená syntax príkazu CREATE TRIGGER:

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER | INSTEAD OF} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

V tejto syntaxi:

Zadajte názov triggera po kľúčovom slove TRIGGER.
Určte časovanie, ktoré spôsobí spustenie triggera. Môže to byť pred BEFORE alebo po AFTER udalosti, ktorá vyvolala trigger. Kľučové slovo INSTEAD OF sa využíva pre trigger nad pohľadom.
Zadajte udalosť, ktorá vyvolá trigger. Udalosť môže byť INSERT , DELETE, UPDATE alebo TRUNCATE.
Po kľúčovom slove ON zadajte názov tabuľky asociovanej s triggerom.
Špecifikujte typ triggera, ktorý môže byť:
1. FOR EACH ROW z názvu vypovedá, tento typ triggera sa spustí pri vyvolaní zmeny na každom riadku (v prípade triggera nad tabuľkou, jedina dovolená možnosť),
2. FOR EACH STATEMENT, tento typ triggera sa spustí raz, bez ohľadu na to koľko riadkov akcia ktorá spustila trigger ovplyvňuje.
Na záver zadajte meno trigger funkcie po kľúčovom slove EXECUTE PROCEDURE.

Úlohy v bleskovke sa týkajú databázy Hockey s nasledovnou schémou.

Vypíšte súpisku Slovenska za rok 2019. Výsledok nech obsahuje 4 stĺpce: priezvisko hráča, meno hráča, jeho číslo dresu a pozíciu. Záznamy vo výsledku zoraďte podľa pozície tak, že najprv nech sú útočníci, obrancovia a nakoniec brankári.

Vypíšte strelcov gólov za rok 2019. Výsledok nech obsahuje 3 stĺpce: id krajiny, meno hráča (priezvisko + meno oddelené medzerou), počet strelených gólov. Záznamy zoraďte podľa počtu gólov zostupne.

Vypíšte najčastejšie prideľované tresty. Výsledok nech má 3 stĺpce: názov trestu, počet koľkokrát bol udelený, dĺžku trvania tohto typu trestu (v minútach) spolu.

Vypíšte zoznam krajín spolu s celkovým súčtom trvania udelených trestov pre hráčov tejto krajiny. Výsledok nech obsahuje 2 stĺpce: krajina a dĺžka trestov. Záznamy zoraďte podľa dĺžky zostupne.

Zoraďte krajiny podľa počtu získaných bodov bez ohľadu na typ zápasu alebo rok majstrovstiev sveta zostupne. Body vypočítajte nasledovne:

výhra = 2 body,
remíza = 1 bod,
prehra = 0 bodov.

Pracujeme na databáze Movies s nasledovnou schémou.

Vyberte si tabuľku, ktorej vytvoríte "historickú" tabuľku - tabuľku na uchovávanie histórie záznamov pri zmene (UPDATE, DELETE). Vybraná tabuľka môže byť z databázy Movies alebo Hockey (napr. c_city z databázy Movies). Historickú tabuľku nazvite h_tabuľka. Tabuľka nebude obsahovať žiaden primárny kľúč, pretože ten istý záznam môže byť modifikovaný niekoľkokrát (viacero riadkov v h_tabuľka s rovnakým id). Nad vašou h_tabuľkou vytvorte triggre pre UPDATE a DELETE, ktoré prenesú pôvodné dáta z (pred UPDATE alebo DELETE) do historickej tabuľky.

CREATE OR REPLACE FUNCTION movies.fn_trg_c_city()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
--
IF (TG_OP = 'DELETE') THEN
	INSERT INTO movies.h_c_city
	(cit_id,cit_name,cit_inserted,cit_inserted_by,cit_modified ,cit_modified_by ,xcit_cou_id,hcit_inserted)
	SELECT OLD.cit_id,old.cit_name,old.cit_inserted,old.cit_inserted_by,old.cit_modified ,old.cit_modified_by ,old.xcit_cou_id,now() ;
ELSIF (TG_OP = 'UPDATE') THEN
	INSERT INTO movies.h_c_city
	(cit_id,cit_name,cit_inserted,cit_inserted_by,cit_modified ,cit_modified_by ,xcit_cou_id,hcit_inserted)
	SELECT OLD.cit_id,old.cit_name,old.cit_inserted,old.cit_inserted_by,old.cit_modified ,old.cit_modified_by ,old.xcit_cou_id,now() ;
ELSIF (TG_OP = 'INSERT') THEN
	INSERT INTO movies.h_c_city
	(cit_id,cit_name,cit_inserted,cit_inserted_by,cit_modified ,cit_modified_by ,xcit_cou_id,hcit_inserted)
	SELECT NEW.cit_id,NEW.cit_name,NEW.cit_inserted,NEW.cit_inserted_by,NEW.cit_modified ,NEW.cit_modified_by ,NEW.xcit_cou_id,now() ;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER
END;
$function$
;
    
    VYTVORENIE TRIGGERA:

create trigger trg_c_city after
insert
    or
delete
    or
update
    on
    movies.c_city for each row execute function movies.fn_trg_c_city()

Poznámka

V nástroji DBeaver jednoducho vytvoríte trigger na tabuľke (naviažete na tabuľku) tak, že v Database Navigator si rozkliknete danú tabuľku, na ktorej chcete vytovriť trigger. Následne pravým tlačidlom myši nad záložkou Triggers zakliknete Create New Trigger, pomenujete ho a zavoláte príslušnú funkciu:

Vytvorte trigger, ktorý zaloguje príslušné modifikácie (operácie INSERT, UPDATE alebo DELETE) vo Vami zvolenej tabuľke (napr. c_city) do tabuľky t_log. Trigger nech sa spustí len raz. Vytvorené záznamy v tabuľke t_log môžu vyzerať nasledovne:

CREATE OR REPLACE FUNCTION movies.fn_trg_log_c_city()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
--
IF (TG_OP = 'DELETE') THEN
	INSERT INTO movies.t_log
	(log_event,log_time,log_user,log_object,log_value)
	SELECT 'DELETE',now(),session_user,'C_CITY',0;
ELSIF (TG_OP = 'UPDATE') THEN
	INSERT INTO movies.t_log
	(log_event,log_time,log_user,log_object,log_value)
	SELECT 'UPDATE',now(),session_user,'C_CITY',0;
ELSIF (TG_OP = 'INSERT') THEN
	INSERT INTO movies.t_log
	(log_event,log_time,log_user,log_object,log_value)
	SELECT 'INSERT',now(),session_user,'C_CITY',0;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER
END;
$function$
;

TRIGGER

create trigger trg_log_city after
insert
    or
delete
    or
update
    on
    movies.c_city for each statement execute function movies.fn_trg_log_c_city()

Vytvorte si úplne jednoduchú funkciu (kľudne jednoriadkovú), kde sa vyskytne delenie nulou. Zistite, čo Vám vráti volanie takejto funkcie.

CREATE OR REPLACE FUNCTION movies.fn_delenie()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare 
cislo1 int := 10;
cislo2 int := 0;
vysledok int;
	begin
		vysledok := cislo1 / cislo2;
	return vysledok;
	END;
$function$
;

Doplňte do funkcie z predošlej úlohy ošetrenie tejto chyby tak, aby funkcia neskončila s chybou, ale vrátila hodnotu 0. Dokumentácia Vám s úlohou môže pomôcť.

CREATE OR REPLACE FUNCTION movies.fn_delenie()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare 
cislo1 int := 10;
cislo2 int := 0;
vysledok int;
	begin
		vysledok := cislo1 / cislo2;
	return vysledok;
   
EXCEPTION
		when sqlstate '22012' then
		return 0;
	
	END;
$function$
;

Doplňte do fukncie výpis do konzoly, že došlo k deleniu nulou. Pred vypracovaním úlohy si preštudujte tento tutoriál.

CREATE OR REPLACE FUNCTION movies.fn_delenie()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare 
cislo1 int := 10;
cislo2 int := 0;
vysledok int;
	begin
		vysledok := cislo1 / cislo2;
	return vysledok;

EXCEPTION
		when sqlstate '22012' then
		raise notice 'Vo funkcii doslo k deleniu 0';
		return 0;
	
	END;
$function$
;

Nasledujúca úloha obsahuje procedúru, ktorú ste mali vytvoriť v 8.cvičeni v úlohe 2.2. Táto procedúra spraví UPDATE nad zázamom v tabuľke c_city podľa jeho cit_id. Kód sme doplnili tak, aby sme si demonštrovali chybový stav. Preštudujte si význam kľúčového slova STRICT. Túto procedúru si skopírujte a spustite ju. Preštudujte si, čo kód a podrobnosti tohto chybového stavu.

CREATE OR REPLACE PROCEDURE movies.pr_c_city_upd(cit_id integer, cit_name text, xcit_cou_id text, cit_modified_by text DEFAULT NULL::text)
 LANGUAGE plpgsql
AS $procedure$
declare
idecko integer; -- ku ulohe no_data_found
	begin
		select c_city.cit_id into strict idecko from c_city where c_city.cit_id = pr_c_city_upd.cit_id; -- ku ulohe no_data_found
		
		update c_city
		set cit_name = pr_c_city_upd.cit_name, 
			xcit_cou_id = pr_c_city_upd.xcit_cou_id, 
			cit_modified_by = coalesce(pr_c_city_upd.cit_modified_by, "session_user"()::text), 
			cit_modified = now()
		where c_city.cit_id = pr_c_city_upd.cit_id
		;
  
	END;
$procedure$
;

Ošetrite chybu z predchádzajúcej úlohy. Procedúra nech užívateľovi vypíše zrozumiteľné chybové hlásenie v slovenčine.

CREATE OR REPLACE PROCEDURE movies.pr_c_city_upd(cit_id integer, cit_name text, xcit_cou_id text, cit_modified_by text DEFAULT NULL::text)
 LANGUAGE plpgsql
AS $procedure$
declare
idecko integer; -- ku ulohe no_data_found
	begin
		select c_city.cit_id into strict idecko from c_city where c_city.cit_id = pr_c_city_upd.cit_id; -- ku ulohe no_data_found
		
		update c_city
		set cit_name = pr_c_city_upd.cit_name, 
			xcit_cou_id = pr_c_city_upd.xcit_cou_id, 
			cit_modified_by = coalesce(pr_c_city_upd.cit_modified_by, "session_user"()::text), 
			cit_modified = now()
		where c_city.cit_id = pr_c_city_upd.cit_id
		;
	
	EXCEPTION
        WHEN NO_DATA_FOUND then -- ku ulohe no_data_found
        	RAISE notice 'Záznam s  ID: % nebol nájdený.', pr_c_city_upd.cit_id;-- ku ulohe no_data_found
		WHEN OTHERS then 
     		raise;		

	END;
$procedure$
;

Upozornenie

Po vypracovaní úloh z cvičenia prosím o vyplnenie nasledujúceho dotazníka.

Ciele

Úvod

Postup

Krok 1

Úloha 1.1

Úloha 1.2

Úloha 1.3

Úloha 1.4

Úloha 1.5

Krok 2

Úloha 2.1

Riešenie

Poznámka

Úloha 2.2

Riešenie

Krok 3

Úloha 3.1

Riešenie

Úloha 3.2

Riešenie

Úloha 3.3

Riešenie

Úloha 3.4

Úloha 3.5

Riešenie

Upozornenie

Zdroje