Eljárások és függvények létrehozása PL/pgSQL nyelven

A modern adatbázis-kezelő rendszerek (DBMS) már rég nem csupán adatok tárolására és lekérdezésére szolgálnak. Sokkal inkább komplex, intelligens entitások, amelyek képesek üzleti logika végrehajtására, automatizált feladatok ellátására és a teljesítmény optimalizálására. A PostgreSQL, mint az egyik legfejlettebb nyílt forráskódú relációs adatbázis, kiválóan támogatja ezt a képességet a PL/pgSQL nyelvével. Ez a procedurális nyelv lehetővé teszi számunkra, hogy robusztus függvényeket és eljárásokat hozzunk létre, amelyek az adatbázis szívében élnek és működnek. Ebben a cikkben részletesen bemutatjuk, hogyan hozhatunk létre ilyen hatékony és rugalmas programozási egységeket.

Miért van szükségünk függvényekre és eljárásokra PL/pgSQL-ben?

Mielőtt belevetnénk magunkat a szintaktikai részletekbe, érdemes megérteni, miért is olyan hasznosak ezek az eszközök:

Kód-újrafelhasználhatóság: A gyakran ismétlődő műveleteket egyszer írjuk meg, majd tetszőleges számú alkalommal hívhatjuk.
Modularitás és karbantarthatóság: A komplex üzleti logikát kisebb, kezelhetőbb egységekre bonthatjuk, ami megkönnyíti a kód megértését és karbantartását.
Teljesítmény optimalizálás: Az adatbázisban végrehajtott kód gyakran gyorsabb, mintha az alkalmazás rétegében futna, mivel minimalizálja a hálózati kommunikációt.
Adatintegritás és biztonság: A komplex szabályokat és ellenőrzéseket az adatbázis szintjén érvényesíthetjük, garantálva az adatok konzisztenciáját. Ezenfelül a felhasználók csak a függvényeken/eljárásokon keresztül érhetik el az adatokat, anélkül, hogy közvetlenül manipulálnák a táblákat.
Tranzakciókezelés: Az eljárások különösen alkalmasak arra, hogy összetett tranzakciókat kezeljenek több adatbázis-műveletet magukba foglalva.

A PL/pgSQL alapjai: A blokkstruktúra

A PL/pgSQL kód mindig egy blokkban szerveződik, amelynek alapvető struktúrája a következő:

[ DECLARE
    deklarációk; ]
BEGIN
    utasítások;
[ EXCEPTION
    hibakezelő utasítások; ]
END;

DECLARE: Opcionális rész, ahol a lokális változókat, kurzorokat és egyéb deklarációkat helyezzük el.
BEGIN…END: Ez a rész tartalmazza a tényleges végrehajtandó logikát és SQL utasításokat. Ez a blokk a PL/pgSQL program alapja.
EXCEPTION: Opcionális rész, amely a hibakezelésre szolgál. Itt definiálhatjuk, hogyan reagáljon a program bizonyos hibákra.

Függvények létrehozása PL/pgSQL-ben

A függvények (functions) a PL/pgSQL leggyakrabban használt programozási egységei. Mindig valamilyen értéket adnak vissza, és SQL lekérdezések részeként is használhatók.

Az alapvető szintaktika: CREATE FUNCTION

Egy egyszerű függvény létrehozása a CREATE FUNCTION paranccsal történik:

CREATE [OR REPLACE] FUNCTION fuggveny_nev (
    [paraméter_1 IN típus_1,
     paraméter_2 IN típus_2,
     ...,
     OUT kimeneti_paraméter_1 típus_kimenet_1,
     OUT kimeneti_paraméter_2 típus_kimenet_2]
)
RETURNS visszateresi_tipus [AS $$ ... $$ | IMMUTABLE | STABLE | VOLATILE | SECURITY INVOKER | SECURITY DEFINER]
LANGUAGE plpgsql
AS $$
-- PL/pgSQL blokk kezdete
[ DECLARE
    valtozo_deklaraciok; ]
BEGIN
    -- Függvény logikája
    RETURN valami_ertek;
END;
$$;

fuggveny_nev: A függvény egyedi neve.
paraméter_1 IN típus_1: Bemeneti paraméterek, amelyek a függvénynek átadott értékeket képviselik. Az IN kulcsszó opcionális, alapértelmezett.
OUT kimeneti_paraméter_1 típus_kimenet_1: Kimeneti paraméterek, amelyek lehetővé teszik, hogy a függvény több értéket is visszaadjon. Ebben az esetben a RETURNS záradékban RECORD-ot vagy TABLE-t kell megadnunk, vagy a kimeneti paraméterek kombinációja lesz a visszatérési típus.
RETURNS visszateresi_tipus: Ez a rész adja meg a függvény által visszaadott érték típusát (pl. INTEGER, TEXT, BOOLEAN, DATE, vagy összetettebb típusok, mint RECORD, SETOF record vagy TABLE).
LANGUAGE plpgsql: Megadja, hogy a függvény PL/pgSQL nyelven íródott.
AS $$ … $$: Ez a dollár idézőjeles blokk (más néven „dollar-quoted string”) tartalmazza a függvény tényleges kódját. Ez azért praktikus, mert így nem kell a kód belsejében lévő apostrofokat escape-elni.

Példa egy egyszerű függvényre

Készítsünk egy függvényt, amely két számot ad össze:

CREATE OR REPLACE FUNCTION osszead (a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

-- Hívás:
SELECT osszead(5, 3); -- Eredmény: 8

Visszatérési típusok: Skaláris értékek, SETOF és TABLE

Skaláris értékek: Egyetlen adat típusú értéket ad vissza (pl. INTEGER, TEXT).
SETOF rekordok: Ha a függvény több sort akar visszaadni, például egy lekérdezés eredményét. Ilyenkor a RETURNS SETOF valamilyen_tabla_neve vagy RETURNS SETOF RECORD formátumot használjuk. Utóbbi esetben a hívó alkalmazásnak kell deklarálnia az oszlopok típusát, vagy egy OUT paraméterekkel definiált táblát kell használni.
TABLE: Ez egy kényelmesebb módja a SETOF RECORD-nak, ahol a kimeneti oszlopok neveit és típusait közvetlenül a RETURNS TABLE (...) záradékban adhatjuk meg.

Példa SETOF TABLE visszatérési típusra:

CREATE TABLE termekek (
    id SERIAL PRIMARY KEY,
    nev VARCHAR(100),
    ar NUMERIC(10, 2)
);

INSERT INTO termekek (nev, ar) VALUES ('Laptop', 1200.00), ('Egér', 25.00), ('Billentyűzet', 75.00);

CREATE OR REPLACE FUNCTION olcso_termekek (max_ar NUMERIC)
RETURNS TABLE (termek_id INTEGER, termek_nev VARCHAR(100), termek_ar NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT id, nev, ar
    FROM termekek
    WHERE ar <= max_ar;
END;
$$;

-- Hívás:
SELECT * FROM olcso_termekek(100.00);
-- Eredmény:
-- termek_id | termek_nev  | termek_ar
-- ----------+-------------+----------
-- 2         | Egér        | 25.00
-- 3         | Billentyűzet| 75.00

Függvények haladó funkciói

Volatilitás (IMMUTABLE, STABLE, VOLATILE):
- IMMUTABLE: A függvény mindig ugyanazt az eredményt adja vissza ugyanazon bemeneti paraméterekkel, és nem módosítja az adatbázis állapotát. A PostgreSQL optimalizálhatja ezeket a függvényhívásokat (pl. gyorsítótárazás).
- STABLE: A függvény ugyanazon SQL utasítás vagy tranzakció során mindig ugyanazt az eredményt adja ugyanazon bemeneti paraméterekkel. Függhet adatbázis-változásoktól, de nem módosítja azokat.
- VOLATILE: A függvény bármikor eltérő eredményt adhat vissza, még ugyanazon paraméterekkel is. Ez az alapértelmezett.
```
CREATE FUNCTION get_current_time() RETURNS TIMESTAMPTZ
LANGUAGE plpgsql
STABLE -- Mivel az idő pillanatfelvétel, egy tranzakción belül stabil.
AS $$
BEGIN
    RETURN NOW();
END;
$$;
```
Biztonsági kontextus (SECURITY INVOKER vs. SECURITY DEFINER):
- SECURITY INVOKER (alapértelmezett): A függvényt az a felhasználó jogosultságaival hajtja végre, aki meghívta.
- SECURITY DEFINER: A függvényt a létrehozó felhasználó jogosultságaival hajtja végre. Ez hasznos lehet, ha egy alacsonyabb jogosultságú felhasználó számára szeretnénk engedélyezni bizonyos, egyébként tiltott műveleteket egy jól kontrollált függvényen keresztül. Ebben az esetben kiemelten fontos a függvény gondos megtervezése és biztonsági auditja.
```
CREATE FUNCTION admin_feladat() RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Itt olyan műveletek hajthatók végre, amelyeket csak az admin felhasználó tehetne meg.
    -- NAGYON ÓVATOSAN KELL HASZNÁLNI!
END;
$$;
```

Hibakezelés (EXCEPTION): A BEGIN...EXCEPTION...END blokk segítségével robusztusabbá tehetjük a függvényeinket.

CREATE OR REPLACE FUNCTION biztonsagos_osztas (szamlalo NUMERIC, nevezo NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN szamlalo / nevezo;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Nullával való osztás történt. 0-t adok vissza.';
        RETURN 0;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Ismeretlen hiba az osztás során: %', SQLERRM;
        -- VAGY: RETURN NULL;
END;
$$;

-- Hívás:
SELECT biztonsagos_osztas(10, 2); -- 5
SELECT biztonsagos_osztas(10, 0); -- NOTICE: Nullával való osztás... Eredmény: 0

Változók és vezérlési szerkezetek: A PL/pgSQL támogatja a lokális változókat (DECLARE blokk), kondicionális utasításokat (IF/ELSIF/ELSE), ciklusokat (LOOP, WHILE, FOR).
Dinamikus SQL (EXECUTE): Lehetővé teszi SQL lekérdezések futtatását, amelyek futásidőben dinamikusan épülnek fel. Ez rendkívül erőteljes, de biztonsági kockázatokat (SQL injekció) rejt magában, ha nem megfelelően kezeljük. Mindig használjunk USING záradékot a paraméterek átadására!

Eljárások (Procedurák) létrehozása PL/pgSQL-ben

A PostgreSQL 11-től kezdve elérhetőek az eljárások (procedures) is, amelyek kulcsfontosságú különbségeket mutatnak a függvényekhez képest, főként a tranzakciókezelés terén.

Az alapvető szintaktika: CREATE PROCEDURE

Egy eljárás létrehozása a CREATE PROCEDURE paranccsal történik:

CREATE [OR REPLACE] PROCEDURE eljaras_nev (
    [paraméter_1 IN típus_1,
     paraméter_2 IN típus_2,
     ...,
     OUT kimeneti_paraméter_1 típus_kimenet_1]
)
LANGUAGE plpgsql
AS $$
-- PL/pgSQL blokk kezdete
[ DECLARE
    valtozo_deklaraciok; ]
BEGIN
    -- Eljárás logikája
    -- INSERT, UPDATE, DELETE utasítások
    -- ESETLEG: COMMIT; vagy ROLLBACK;
END;
$$;

eljaras_nev: Az eljárás egyedi neve.
Paraméterek: Hasonlóan a függvényekhez, IN (bemeneti) és OUT (kimeneti) paramétereket is fogadhat.
Nincs RETURNS záradék: Ez a legfontosabb különbség a függvényekhez képest! Az eljárások nem adnak vissza közvetlenül értéket a RETURNS kulcsszóval (bár az OUT paramétereken keresztül képesek értékeket „visszaszolgáltatni”).
LANGUAGE plpgsql és AS $$ … $$: Ugyanúgy működik, mint a függvényeknél.

Eljárások hívása: CALL

Az eljárásokat a CALL paranccsal hívjuk meg, nem pedig SELECT-tel, mint a függvényeket:

CALL eljaras_nev(parameter_ertekek);

Fő különbség: Tranzakciókezelés eljárásokban

Az eljárások legnagyobb előnye, hogy képesek saját tranzakciókat kezelni. Ez azt jelenti, hogy egy eljáráson belül használhatunk COMMIT és ROLLBACK utasításokat, ami függvényekben nem engedélyezett.

Ez rendkívül hasznos összetett, több lépcsős műveletek automatizálására, ahol minden lépés egy különálló tranzakció. Például egy pénzügyi átutalási rendszerben, ahol több tábla érintett, és minden részfeladatot külön tranzakcióban szeretnénk biztosítani.

Példa egy eljárásra tranzakciókezeléssel

Készítsünk egy eljárást, amely kezeli két felhasználó közötti pénzátutalást, figyelembe véve a tranzakciókezelést:

CREATE TABLE felhasznalok (
    id SERIAL PRIMARY KEY,
    nev VARCHAR(100) NOT NULL,
    egyenleg NUMERIC(10, 2) NOT NULL DEFAULT 0.00
);

INSERT INTO felhasznalok (nev, egyenleg) VALUES ('Alice', 1000.00), ('Bob', 500.00);

CREATE OR REPLACE PROCEDURE penz_utalasa (
    felado_id INTEGER,
    cimzett_id INTEGER,
    osszeg NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    felado_egyenleg NUMERIC;
BEGIN
    -- Ellenőrizzük, hogy van-e elég pénz a feladónál
    SELECT egyenleg INTO felado_egyenleg FROM felhasznalok WHERE id = felado_id FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Feladó (ID: %) nem található.', felado_id;
    END IF;

    IF felado_egyenleg < osszeg THEN
        RAISE EXCEPTION 'Nincs elegendő fedezet a feladónál (ID: %).', felado_id;
    END IF;

    -- Feladó egyenlegének csökkentése
    UPDATE felhasznalok
    SET egyenleg = egyenleg - osszeg
    WHERE id = felado_id;

    -- Cimzett egyenlegének növelése
    UPDATE felhasznalok
    SET egyenleg = egyenleg + osszeg
    WHERE id = cimzett_id;

    -- Ha minden sikeres volt, véglegesítjük a tranzakciót.
    -- Fontos: Ez a COMMIT utasítás csak procedure-ben engedélyezett!
    COMMIT;

    RAISE NOTICE 'Sikeres átutalás % ID-ről % ID-re, összeg: %', felado_id, cimzett_id, osszeg;

EXCEPTION
    WHEN OTHERS THEN
        -- Hiba esetén visszavonjuk a tranzakciót.
        -- Fontos: Ez a ROLLBACK utasítás csak procedure-ben engedélyezett!
        ROLLBACK;
        RAISE NOTICE 'Hiba történt az átutalás során: %', SQLERRM;
END;
$$;

-- Hívás:
-- CALL penz_utalasa(1, 2, 200.00); -- Sikeres átutalás
-- SELECT * FROM felhasznalok; -- Alice: 800, Bob: 700

-- Hiba esetén (pl. nincs elég pénz):
-- CALL penz_utalasa(1, 2, 900.00); -- Hiba, mert Alice-nek csak 800 van.
-- SELECT * FROM felhasznalok; -- Egyenlegek változatlanok maradnak a ROLLBACK miatt.

Ez a példa jól illusztrálja az eljárások erejét a komplex, több adatbázis-műveletet magában foglaló üzleti logikák kezelésében, ahol elengedhetetlen a tranzakció integritásának fenntartása.

Függvények vs. Eljárások: Mikor melyiket használjuk?

Habár mindkettő lehetővé teszi a PL/pgSQL kód futtatását az adatbázisban, kulcsfontosságú különbségek vannak:

Visszatérési érték: A függvények mindig visszaadnak valamilyen értéket (akár VOID-ot is), és használhatók SQL lekérdezések (pl. SELECT, WHERE) részeként. Az eljárások nem adnak vissza értéket a RETURNS kulcsszóval, de OUT paramétereken keresztül „szolgáltathatnak” adatokat.
Tranzakciókezelés: Ez a legnagyobb különbség. Az eljárások képesek COMMIT és ROLLBACK utasításokat tartalmazni, így önállóan kezelhetik a tranzakciókat. A függvények egy külső tranzakció részeként futnak, és nem végezhetnek COMMIT/ROLLBACK műveleteket (az a hívó feladata).
Hívás módja: A függvényeket a SELECT paranccsal, míg az eljárásokat a CALL paranccsal hívjuk meg.
Használati esetek:
- Függvények: Ideálisak számításokhoz, adatok szűréséhez, formázásához, vagy olyan logikákhoz, amelyek egyetlen értéket vagy értékek halmazát (táblát) adnak vissza, és integrálódnak az SQL lekérdezésekbe.
- Eljárások: Ideálisak összetett üzleti logikákhoz, adatmanipulációs feladatokhoz, amelyek több lépésből állnak és saját tranzakciókezelést igényelnek (pl. adatmigráció, komplex jelentéskészítés, üzleti folyamatok automatizálása).

Bevált gyakorlatok és tippek a PL/pgSQL kódolásához

A hatékony és karbantartható PL/pgSQL kód írásához érdemes néhány bevált gyakorlatot követni:

Modularitás és újrafelhasználhatóság: Bontsuk fel a komplex logikát kisebb, specifikus feladatokat ellátó függvényekre/eljárásokra. Ez megkönnyíti a tesztelést és a hibakeresést.
Tiszta és olvasható kód: Használjunk konzisztens formázást, értelmes változó- és függvényneveket. A kommentekkel magyarázzuk el a komplex részeket. A -- egysoros és a /* ... */ többsoros kommentek egyaránt használhatók.
Hibakezelés: Mindig vegyünk figyelembe hibakezelési forgatókönyveket az EXCEPTION blokk használatával. Gondoskodjunk róla, hogy a hibák ne vezessenek adatintegritási problémákhoz, és adjunk vissza értelmes hibaüzeneteket.
Teljesítmény optimalizálás:
- Kerüljük a felesleges hurokismétléseket; sok esetben egy jól megírt SQL lekérdezés sokkal hatékonyabb.
- Használjunk RETURN QUERY vagy RETURN NEXT utasításokat a SELECT INTO ciklus helyett, ha több sort adunk vissza.
- Vigyázzunk a dinamikus SQL (EXECUTE) használatával; bár rugalmas, könnyen okozhat teljesítményproblémákat, ha nem megfelelően paraméterezzük.
- Válasszuk ki a megfelelő volatilitási szintet (IMMUTABLE, STABLE, VOLATILE), hogy a PostgreSQL jobban tudja optimalizálni a hívásokat.
Biztonság:
- Legyünk rendkívül óvatosak a SECURITY DEFINER függvényekkel, mivel azok a létrehozó jogosultságaival futnak. Csak abszolút szükséges esetben használjuk, és alaposan teszteljük.
- Dinamikus SQL esetén mindig használjunk USING záradékot a paraméterek átadására az SQL injekció elkerülése érdekében.
- Adjuk meg a legszükségesebb jogosultságokat a felhasználóknak a függvények és eljárások futtatására.
Tesztelés: Alaposan teszteljük a függvényeket és eljárásokat különböző bemeneti adatokkal és határfeltételekkel, beleértve a hibás forgatókönyveket is.

Konklúzió

A PL/pgSQL nyelven írt eljárások és függvények a PostgreSQL adatbázisok alapvető építőkövei. Lehetővé teszik a fejlesztők számára, hogy komplex üzleti logikát valósítsanak meg közvetlenül az adatbázison belül, javítva a teljesítményt, a biztonságot és a karbantarthatóságot. A függvények ideálisak számításokhoz és SQL-be integrálható logikákhoz, míg az eljárások kiválóak összetett tranzakciók kezelésére és több lépésből álló feladatok automatizálására. A megfelelő eszköz kiválasztása, a bevált gyakorlatok követése és a gondos tervezés kulcsfontosságú a robusztus és hatékony adatbázis-alkalmazások fejlesztéséhez. Kezdjen el kísérletezni, és fedezze fel a PL/pgSQL nyújtotta lehetőségeket!