Andmebaasid 2023/24 kevad

Funktsioonide ja protseduuride loomine

Sissejuhatus

Funktsioone kasutatakse päringute lihtsustamiseks ja abistamiseks. Andmebaasid sisaldavad endas hulga juba varem valmis tehtud funktsioone, mis on mõeldud kasutajate elu lihtsustamiseks. Näiteks oleme varem kasutanud funktsiooni LENGTH, mis tagastab teksti pikkuse

SELECT LENGTH(eesnimi) FROM isikud;

Juhul, kui kasutaja ei leia juba olemasolevate funktsioonide hulgast vajaliku, siis on võimalus kasutajal ka oma funktsioone (UDF ingl - user defined function) luua.

Protseduurid kirjutavad/muudavad (sisestavad, uuendavad, kustutavad) andmebaasi. Protseduure harilikult kasutaja otse ei käivita vaid protseduur käivitatakse harilikult taustal mingi kokkulepitud tegevuse korral. Protseduurid realiseerivad tihti ärireegleid.

Funktsiooni loomine

Vaatame PostgreSQLis nii SQLis kui ka protseduurilises SQL'is funktsioonide loomist, mida käsitlesime juba ka loengus. Esmalt vaatame protseduurilises SQLis funktsiooni loomist.

Funktsiooni loomise süntaks protseduurilises SQLis:

CREATE FUNCTION <funktsiooni_nimi> ([{parameetrid}])
RETURNS <andmetüüp>
LANGUAGE plpgsql
AS $$
[DECLARE....]
    BEGIN
        ....
        RETURN [väärtus]
    END;
$$

Loome esmalt lihtsa funktsiooni, mis väljastaks meile ühe veeruna komaga eraldatult perenime ja eesnime ning selle näite põhjal vaatame funktsiooni loomise sisse.

CREATE FUNCTION f_nimi (a_e varchar(100), a_p varchar(100))
RETURNS varchar(202)
LANGUAGE plpgsql
AS $$
DECLARE nimi varchar(202);
    BEGIN
        nimi := a_p||', '|| a_e;
        RETURN nimi;
    END;
$$

Siin esimesel real andsime loodavale funktsioonile nime (lepime kokku, et meie aines on funktsiooni nime ees f_, kuid see on erinevates töörühmades ja organisatsioonides erinev) ning määrame parameetrid koos andmetüübiga. Kuigi funkstioonil PostgreSQLis saab olla lisaks sisendparameetritele IN (vaikimisi on parameetrid sisendparameetrid) ka väljundparameetreid OUT ja INOUT parameetreid, siis meie oma kursusel kasutame vaid sisendparameetreid funktsiooni korral. Antud näite puhul tähendab, et me peame ette andma kaks varchar(100) andmetüübiga parameetrit.

RETURNS real anname teada, millise andmetüübi funktsioon peab väljastama. NB! Veenduge, et andmetüüp saab korrektselt määratud! Näiteks antud näites ei piisaks sellest, et paneme varchar(200), sest lisaks ees- ja perenimele on vaja ruumi ka koma ja tühiku jaoks. Küll oleks võinud siin olla näiteks ka varchar(220).

LANGUAGE määrab ära, millises keeles on funktsioon kirjutatud. Lisaks SQL'ile ja protseduurilisele SQL'ile, mida meie oma kursusel vaatame on võimalik kasutada PostgreSQLis ka Pyhtonit ja C keeli.

Protseduurilise SQL keele korral funktsioon kirjutatakse AS $$ ja $$ vahele.

Esmalt defineerime muutuja nimi, mis on varchar(202) andmetüübiga. Muutujate nimed (aga ka funktsiooni nimed) võiks olla tähenduslikud. Arvatavasti märkasite, et muutuja andmetüüp on sama, mis väljastatav andmetüüp. Alati ei pruugi see nii olla, sest mõnikord on meil vaja funktsiooni sees defineerida ka mitu muutujat, millest vaid üks on väljastatav.

BEGIN ja END vahele kirjutame, mida funktsioon peaks tegema. Eelnevalt defineeritud muutujale omistame (omistamise sümbol on :=) väärtuse, kus kasutame funktsiooni defineerimisel toodud parameetreid. RETURN real kirjutame muutuja, mille väärtuse väljastame.

DBeaver rakenduses asuvad juba loodud funktsioonid ja protseduurid “Function” menüü all. Juhul kui oleme loonud uue funktsiooni või protseduuri, siis uuendame funktsioonide vaadet (Joonis).

Loodud funktsiooni saame kasutada päringulausetes:

SELECT f_nimi(eesnimi, perenimi) FROM isikud;

või paneme selle hoopis päringulause tingimusse:

SELECT eesnimi, perenimi FROM isikud
WHERE LENGTH(f_nimi(eesnimi, perenimi)) > 15;

Viimasel juhul saame sellise vastuse:

Päringute juures rääkisime, et kindlasti peab päringulauses olema kaks osa: SELECT ja FROM. Proovige aga päringuid

SELECT f_nimi('Mikk','Saar');
SELECT * FROM f_nimi('Mikk', 'Saar');

Töötab. Katsetage ka

SELECT USER, now();

PostgreSQLis töötavad sellised päringud ilma FROM osata, kuid mõnedes andmebaasi juhtimissüsteemides (näiteks Oracle, DB2) ei tööta. Selliste juhtimissüsteemide korral on vaja kasutada süsteemitabelit sys.dummy sellistes päringutes, kus ei ole FROM osa ehk siis kus me midagi konkreetsest tabelist ei vaja.

Kindlasti tuleb silmas pidada ka seda, et selle loodud funktsiooni f_nimi kasutamisel ei kontrollita, et me kindlasti liidaks ees- ja perenime. Näiteks töötab ka selline päring:

SELECT f_nimi(nimi, perenimi) 
FROM isikud JOIN klubid ON isikud.klubis=klubid.id;

Tulemuse algus on selline

Mõelge, kas võiks töötada ka selline päring:

SELECT f_nimi(valge_tulemus, must_tulemus) 
FROM partiid; 

Kontrollige oma oletust.

Funktsiooni sees saame kasutada ka päringut. Loome funktsiooni, mis vastavalt id väärtusele tagastab mängija eesnime ja perenime.

CREATE FUNCTION f_nimed (a_id integer)
RETURNS varchar(103)
LANGUAGE plpgsql
AS $$
    DECLARE d_enimi varchar(50);
    DECLARE d_pnimi varchar(50);
    BEGIN
         SELECT eesnimi, perenimi INTO d_enimi, d_pnimi FROM isikud
         WHERE id=a_id;
         RETURN d_enimi ||', '|| d_pnimi;
    END;
$$

Selles funktsioonis defineerisime kaks muutujat (d_enimi ja d_pnimi), millele andsime päringuga väärtused. Siin funktsiooni loomises INTO näitab, millisele muutujale antud päringu väärtus omistada. Näites tegime seda ühe päringuga, kuid oleks võinud ka kahte päringut kasutada ja kumbagi muutujasse eraldi päringuga väärtus omistada:

CREATE FUNCTION f_nimed (a_id integer)
RETURNS varchar(103)
LANGUAGE plpgsql
AS $$
    DECLARE d_enimi varchar(50);
    DECLARE d_pnimi varchar(50);
    BEGIN
        SELECT eesnimi INTO d_enimi FROM isikud
        WHERE id=a_id;
        SELECT perenimi INTO d_pnimi FROM isikud
        WHERE id=a_id;
        RETURN d_enimi ||', '|| d_pnimi;
    END;
$$;

Funktsioonis võib küll kasutada päringut, mis väljastavad mitut rida, kuid silmas tuleb pidada, et INTO omistamisega päringutes peab olema üherealine päringu vastus kui @RETURNS@ pole tefineeritud väljastama tabelit.

Päring

SELECT f_nimed(83); 

annab tulemuseks

Funktsioone saab ka muuta

CREATE OR REPLACE FUNCTION <funktsiooni_nimi>....

Samuti saab funktsiooni kustutada.

DROP FUNCTION <funktsiooni_nimi>

---

Loome funktsiooni, mis võtab sisendiks integer tüüpi arvu, liidab ühe juurde ning väljastab integer tüüpi arvu. .

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Paneme tähele, kuidas funktsiooni päises toodud muutujat i kasutatakse funktsiooni sees.

Funktsiooni saame kasutada.

SELECT increment(2);
SELECT * FROM increment(2);
SELECT eesnimi, perenimi, increment(2) FROM isikud;

Funktsiooni loomise üldine formaat on.

CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
LANGUAGE lang_name
AS 'definition';

Alljärgnevalt selgitame funktsiooni loomise lauseehituse osasid eraldi:

• name - funktsioonile antav nimi;
• argname - funktsiooni päises oleva argumendi nimi (nimed);
• argtype - funktsiooni päises toodud argumendi (argumentide) andmetüüüp (andmetüübid);
• rettype - funktsiooni poolt tagastatava väärtuse (väärtuste) andmetüüp (andmetüübid) või struktuur, kui rettype on tabel;
• lang_name - funktsiooni sisu loomiseks kasutatav keel. Käesolevas materjalis kasutame SQL keelt;

definition - funktsiooni sisu vastavalt valitud keelele. SQL keele puhul on funktsiooni definitsiooniks SQL lause koos.

PostgreSql funktsiooni loomisel võib lisaks PL/pgSQL keelele kasutada veel SQL, PL/Tcl ja C programmeerimiskeelt. SQL keelega loodud funktsioonide näiteid toome allpool.

Vaatame ülal loodud funktsiooni increment loomise SQL lauset.

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Antud juhul on funktsiooni nimi (name) increment.
Funktsiooni argument/sisend (argname) on antud juhul i.
Argumendile i määratakse, mis andmetüüpi (argtype) ta on. Antud juhul on i integer andmetüüpi.
increment funktsioon tagastab samuti integer andmetüübi, mille me määrame rettype kaudu.
Antud peatükis (lang_name), milles funktsiooni sisu (definition) kirjutame on plpgsql ja SQL. SQL keel on lihtsamate funktsioonide jaoks.

Olemasolevate funktsioonide ja protseduuride vaatamine

Erinevad võimalused funktsioone kasutada

Näide 1

Loodud funktsiooni saab pärida erineval moel.

SELECT increment (2);
SELECT * FROM increment (3);
SELECT eesnimi, perenimi, increment(2) FROM isikud;
SELECT eesnimi, perenimi, increment(id) FROM isikud;

Viimases päringus kasutame increment funktsiooni sisendina isiku id välja.

Näide 2

Loome funktsiooni, mille sisend on tühi, aga väljastab isikute tabelist eesnime ja perenime.
Paneme tähele, et väjundiks defineerime tabeli, mille puhul anname ette välja nimed ja - tüübid. Paneme tähele, et antud näites kasutame LANGUAGE SQL SQL keeles on funktsioone lihtsam luua.

CREATE FUNCTION getIsikud() 
RETURNS TABLE (e_nimi varchar(25), p_nimi varchar(25))
AS 'SELECT eesnimi, perenimi FROM isikud;'
LANGUAGE SQL;

Pärime funktsiooni kahel erineval viisil.

SELECT getIsikud();
SELECT * FROM getIsikud() ;

Esimesel juhul väljastatakse tulemus ühe väljana.
Teisel on tulemus tabeli kujul - nagu funktsiooni loomisel defineerisime.

Samuti võime päringus tärn asemel defineerida välja või väljad, mida soovime tagastatavas tulemuses näha.

SELECT e_nimi FROM getIsikud() ;

Sarnaselt varemõpitud SELECT päringule, saame tabeli kujul päringule rakendada kõiki SELECT päringu osi, näiteks WHERE tingimusi.

SELECT * FROM getIsikud() WHERE e_nimi  = 'Arvo' ;

Näide 3

Soovides filtreerimine teha funktsiooni sees, siis kasutades OR REPLACE uuendame ülalloodud funktsiooni. Paneme tähele, et nüüd anname funktsiooni päises kaasa ka sisendi nime - name ja sisendi andmetüübi - varchar(25).

CREATE OR REPLACE  FUNCTION getIsikud(name varchar(25)) 
RETURNS TABLE (e_nimi varchar(25), p_nimi varchar(25))
AS SELECT eesnimi, perenimi FROM isikud WHERE eesnimi = name ; '
LANGUAGE SQL;

Pärime funktsiooni kahel erineval kujul.

SELECT getIsikud('Arvo');
SELECT * FROM getIsikud('Arvo');

Näide 4

Loome funktsiooni, mis võtab sisendiks varchar(25) tüübi ja väljastab esimese tähe (char).

CREATE FUNCTION getFirstLetter(f varchar(25)) RETURNS char 
AS 'SELECT LEFT(f, 1) ; '
LANGUAGE SQL;

Rakendame loodud funktsiooni SELECT päringus väljale eesnimi.

SELECT getFirstLetter(eesnimi), perenimi  FROM isikud;

Rakendame loodud funktsiooni SELECT päringus väljale perenimi.

SELECT getFirstLetter(perenimi), perenimi  FROM isikud;

Funktsiooni kustutamine

DROP FUNCTION funktsiooni_nimi;

Kustutame loodud funktsioonid.

DROP FUNCTION add;
DROP FUNCTION getIsikud;
DROP FUNCTION getFirstLetter ;

Enesekontroll

On vaja luua funktsioon, mis võtab sisendiks kaks (a ja b) integer argumenti. Funktsioon väljastab tõeväärtus tüüpi (boolean) tulemuse true, kui a = b, vastasel juhul väljastatakse false

Link PostgreSQL dokumentatsioonile

Protseduuri loomine

CREATE [ OR REPLACE ] PROCEDURE name ( [ [ argname ] argtype ] )
AS 'definition'
LANGUAGE lang_name;

Protseduuri loomise formaat sarnaneb suures osas funktsiooni loomisele omale. Põhiline erinevus seisneb selles, et protseduur ei tagasta tulemust, seega ei ole ka loomisel vaja täpsustada tagastatavat struktuuri.

Ütleme, et ärireegel nõuab, et kõik eesnimed peavad algama suure tähega. Loome protseduuri, mis käivitamisel muudab kõikide eesnimede esimesed tähed suurtähtedeks.

CREATE PROCEDURE first_letter_to_upper() 
AS 'UPDATE isikud SET eesnimi = initcap(eesnimi)'
LANGUAGE SQL;

Uuendame isikud tabelis kõik Urmased väikese tähega.

UPDATE isikud SET eesnimi = 'urmas' WHERE eesnimi = 'Urmas'; 

Kontrollige, et kõik Urmased on tabelis väikese tähega.
Nüüd kasutame loodud protseduuri, mis muudab kõikide eesnimede esimese tähe suureks isikute tabelis.

CALL first_letter_to_upper();

Kontrollime, kas Urmas eesnimed on taas kõik suure algustähega?

Protseduuride kasutamist rakendame peale triggerite õppimist.

Paneme tähele, et funktsiooni kutsusime välja SELECT käsuga, siis protseduuri poole pöördume CALL käsuga.

CASE funktsiooni sees

Protseduuri sees saab kasutada samu operaatoreid, mida SELECT lauses. Siinkohal demonstreerime CASE kasutamist protseduuri sees.
Kuna funktsioonide ja protseduuride sisud võivad minna päris keeruliseks, siis on hea kasutada kommentaare. Paneme tähele, et kasutame SQL stiilis kommntaare - -

CREATE OR REPLACE PROCEDURE  casedemo(a boolean) AS $$
DECLARE
note   varchar(25);  -- muutuja 
BEGIN	
CASE a - - protseduuri sisendist lähtuv CASE
WHEN true THEN note = 'HIGH'; -- kui sisend on true, siis väärtustame muutuja note siin
ELSE note = 'LOW';  -- muul juhul väärtustame siin
END CASE;   -- case lõpp
raise notice '%', note ;     -- Teade konsoolile 
END;
$$ LANGUAGE plpgsql;

Proovi kutsuda protseduuri välja erinevate boolean väärtustega.

IF funktsiooni sees

CREATE OR REPLACE PROCEDURE  ifdemo(a integer, b integer ) AS $$
DECLARE
note   varchar(25);
BEGIN
IF a > b THEN
note = 'a on suurem kui b';
-- kommentaar
ELSIF a < b THEN
note = 'a on väiksem kui b';
ELSE
note = 'a ja b on võrdsed';
END IF;
raise notice '%', note ;     
END;
$$ LANGUAGE plpgsql;

LOOP protseduuri sees

Näide korduse LOOP protseduuri (sama saab ka funktsiooni sees) kasutamisel. Sisendist a sõltub tsükli pikkus.

CREATE or REPLACE PROCEDURE loopdemo(a integer ) AS $$
DECLARE 
note   varchar(25);
i integer:=1;
BEGIN
RAISE NOTICE 'Loop started.';
LOOP
raise notice '%', i ; 
i:=i+1;
EXIT WHEN i>a;
END LOOP;
RAISE NOTICE 'Loop completed';  
END;
$$ LANGUAGE plpgsql;

Kutsu protseduuri välja ja vaata, mis tulemus konsoolil tuleb. Muuda ka protseduuri sisendit ja vaata, kuidas tulemus muutub.

WHILE protseduuri sees

Näide korduse WHILE protseduuri (sama saab ka funktsiooni sees) kasutamisel. Sisendist a sõltub tsükli pikkus.

CREATE OR REPLACE PROCEDURE  whiledemo(a integer) AS $$
DECLARE
note   varchar(25);
i integer:=1;
BEGIN
WHILE i < a LOOP
raise notice 'i %', i;
i := i + 1;
END LOOP; 
END;
$$ LANGUAGE plpgsql;

Kutsu protseduuri välja ja vaata, mis tulemus konsoolil tuleb. Muuda ka protseduuri sisendit ja vaata, kuidas tulemus muutub.

Protseduuri muutmine

Kasutades OR REPLACE muudame protseduuri first_letter_to_upper() selliseks, et ta muudaks ka perenime esitähe suureks

CREATE OR REPLACE  procedure first_letter_to_upper() 
AS 'UPDATE isikud SET eesnimi = initcap(eesnimi), perenimi = initcap(perenimi)'
LANGUAGE SQL;

Testimiseks muudame kõikide isikute perekonnanimed isikud tabelis väikese tähega.

UPDATE isikud SET perenimi = lower(perenimi) ; 

Kontrollige, kas perekonnanimed on väikese tähega? Rakendame protseduuri.

CALL first_letter_to_upper();

Kontrollime kas perekonnanimed algavad suure tähega?

Protseduuri kustutamine

DROP PROCEDURE  protseduuri_nimi;

Kustutame loodud protseduuri:

DROP PROCEDURE first_letter_to_upper;

Enesekontroll

On vaja luua protseduur, mis kustutab isikud tabelist tühja isikukoodiga kirjed.

Link PostgreSQL dokumentatsioonile