Andmebaasid (sessioonõpe) 2023/24 kevad

Funktsioonide loomine

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.

Ühe väljundiga funktsioonide loomine

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

Funktsiooni loomise süntaks protseduurilises SQL'is on:

CREATE FUNCTION <funktsiooni_nimi> ([{parameetrid koos andmetüübiga}])
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 PostgreSQL'is 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 PostgreSQL'is ka Python'it 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.

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. PostgreSQL'is 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 defineeritud väljastama tabelit.

Päring

SELECT f_nimed(83); 

annab tulemuseks

Selle alapunkti lõpetuseks ka SQL keeles funktsiooni koostamine. Üldsüntaks:

CREATE FUNCTION <funktsiooni_nimi> ([{parameetrid}])
RETURNS <andmetüüp>
LANGUAGE SQL
AS $$ SELECT lause.....;$$
;

Nagu näete, siis siin pannakse SELECT lause $$ vahele. Võib ka panna ülakomade vahele, mis DBeaveris kuvatakse kui kommentaar, kuid see seab teatud piirangud SELECT lausele, millest loengus rääkisime ja seepärast me seda ei kasuta. Koostame funktsiooni, millest rääkisime ka loengus ehk siis sellise funktsiooni, mis leiab liikmete arvu klubis klubi id alusel.

CREATE FUNCTION f_klubisuurus(a_id integer)
RETURNS integer
LANGUAGE SQL
AS $$ SELECT count(isikud.id) FROM isikud RIGHT JOIN klubid ON isikud.klubis=klubid.id 
WHERE klubid.id=a_id; $$
;

Kasutamiseks päringus saame samuti erinevatel viisidel:

SELECT f_klubisuurus(51);
SELECT * FROM f_klubisuurus(51);
SELECT f_klubisuurus(id) FROM klubid WHERE nimi='Areng';

Nagu näeme, siis siin ka viimases päringus kuigi on vaja andmeid kahest tabelist (isikud ja klubid), saame kasutada ainult klubide tabelit, mis lihtsustab programmeerija tööd. Tabelid isikud ja klubid seotakse funktsioonis. Saaksime kasutada ka selliselt:

SELECT nimi, f_klubisuurus(id) FROM klubid;

mis annab tulemuseks

Ka siin näeme, et ei pea kasutama GROUP BY, mida tavapäringus kindlasti kasutama peaks. Kuid loengus rääkisime, et see päring töötab aeglasemalt kui päring, mis funktsiooni ei kasuta.

Vaata, kuidas luua nii protseduurilises SQL-is kui ka SQL-is ühe väljundiga funktsiooni:

Tabelit väljastava funktsiooni loomine

Loome kaks analoogilist funktsiooni. Ühe SQL keeles ja teise protseduurilises SQL'is, kus mõlemad väljastavad klubi id alusel klubiliikmete nimekirja.

CREATE FUNCTION f_klubiliikmed1(a_id integer)
RETURNS TABLE 
    (eesnimi varchar(100),
    perenimi varchar(100),
    sugu char(1),
    synniaeg date,
    elo integer)
LANGUAGE SQL
AS $$ SELECT eesnimi, perenimi, sugu, synniaeg, ranking FROM isikud WHERE klubis=a_id; 
$$;

Ja nüüd protseduurilises SQL'is:

CREATE FUNCTION f_klubiliikmed2(a_id integer)
RETURNS TABLE 
    (e_nimi varchar(100),
    p_nimi varchar(100),
    gender char(1),
    s_aeg date,
    elo integer)
LANGUAGE plpgsql
AS $$
BEGIN 
RETURN query SELECT eesnimi, perenimi, sugu, synniaeg, ranking 
FROM isikud WHERE klubis=a_id;
END;
$$;

Miks siin on SQL'is kirjutatud väljastatava tabeli veergude nimed teised kui protseduurilises SQL'is kirjutatud tabelinimed? Katsetage luues funktsioon f_klubiliikmed3 protseduurilises SQL'is pannes samad väljundtabeli veerupealkirjad kui funktsioonil f_klubiliikmed1 ning käivitage see. Kui saate veateate kirjutage SELECT lausesse tunnuste ette tabeli nimed.

Siin kui käivitada funktsioon (ükskõik, kas numbriga 1 või 2) SQL lausega

SELECT * FROM f_klubiliikmed1(51);

saame sellise tulemustabeli:

Kuid SQL päringulause

SELECT f_klubiliikmed1(51);

annab tulemustabeliks:

Kontrollimaks, kas said funktsiooni loomisest aru ava soovi korral test.
AVA TEST

Funktsiooni muutmine ja kustutamine

Funktsioone saab ka muuta

CREATE OR REPLACE FUNCTION <funktsiooni_nimi>....

Kõiki muudatusi ei õnnestu selle käsuga teha, kui näiteks on tabelis vaja andmetüüpi muuta vms. Seega mõnikord on vaja ka funktsioon kustutada ja siis uuesti luua. Funktsiooni saab kustutada

DROP FUNCTION <funktsiooni_nimi>

Vaadete muutmine ja vaate kaudu põhitabeli andmete muutmine

Protseduuride loomine