- Procedury składowane w bazie PostgreSQL
- Procedury składowane w języku SQL
- Procedury składowane w języku PL/pgSQL
- Obsługa błędów w PL/pgSQL - RAISE
- Pętle w PL/pgSQL
- Kursory w języku PL/pgSQL
W ramach bazy danych PostgreSQL istnieje możliwość definiowania własnych funkcji. Do realizacji ich
można wykorzystać język SQL lub wbudowany w bazę danych język PL/pgSQL. Możliwe jest także tworzenie
funkcji z wykorzytsaniem języków proceduralnych tj. C, perl czy python. W tym przypadku przygotowane
biblioteki dołączamy do bazy danych a poprzez polecenia DDL ( CREATE FUNCTION ) tworzymy odpowiednie
obiekty w bazie danych z funkcjonalnością realizowaną przez zewnętrzne programy. W ramach zajęć przedstawione zostaną
funkcje realizowane z wykorzystaniem języka SQL i PL/pgSQL. Podstawowa struktura polecenia tworzącego
funkcję składowaną przedstawia poniższe polecenie.
CREATE FUNCTION nazwa ([typ_danych [, ]])
RETURNS typ_wyniku
AS definicja
LANGUAGE nazwa_jezyka
Polecenia SQL i interfejsu psql pomagające przetwarzać opracowane funkcje.
-- Wyświetlenie funkcji użytkownika - polecenie z interfejsu psql
\df
-- Wyświetlenie funkcji użytkownika - polecenie SQL - tablica pg_proc
\d pg_proc
-- Polecenie SQL wyświetlające zawartość procedury
SELECT prosrc FROM pg_proc WHERE proname='nazwa_funkcji' ;
-- Polecenie modyfikujące procedurę lub je tworzące
CREATE OR REPLASE FUNCTION ...
-- Polecenie usuwające procedurę
DROP FUNCTION nazwa_funkcji([lista typów])
Obsługa błędów w PL/pgSQL - RAISE
Obsługa błędów jest ważnym elementem procedur działających w ramach bazy danych. W ramach
obsługi funkcji mamy możliwość wykorzystania polecenia RAISE, które udostępnia przekazzanie informacji
do użytkownika jak i do systemowego logu. Dodatkowo istnieje możliwość przestałania do użytkownia
standardowego kodu błedu w ramach parametru SQLSTATE.
- Obsługa polecenia RAISE
DO $$
BEGIN
RAISE INFO 'information message %', now() ;
RAISE LOG 'log message %', now();
RAISE DEBUG 'debug message %', now();
RAISE WARNING 'warning message %', now();
RAISE NOTICE 'notice message %', now();
END $$;
- Dodatkowa klauzula USING w ramach RAISE
DO $$
DECLARE
test text := '[błędna wartość]' ;
BEGIN
-- ... kod aplikacji
-- raport o błędzie i podpowiedź
RAISE EXCEPTION 'Błędne dane: %', test
USING HINT = 'Podpowiedź dla użytkownika';
END $$;
- Wysłanie kodu błędu - SQLSTATE
DO $$
BEGIN
RAISE SQLSTATE '00200' ;
END $$;
Realizacja funkcjanalności obsługi błędów - RAISE.
- Wyświetlenie uczestnika kursu, w przypadku braku rekordu sygnalizacja błędu.
-
CREATE OR REPLACE FUNCTION fun4 (iducz int) RETURNS text AS $$
DECLARE
-- iducz int;
rec_uczestnik uczestnik%ROWTYPE;
BEGIN
SELECT INTO rec_uczestnik * FROM uczestnik WHERE id_uczestnik = iducz;
IF NOT FOUND THEN
RAISE EXCEPTION 'Uczestnik % nie jest w bazie', iducz;
END IF;
RETURN rec_uczestnik.imie || ' ' || rec_uczestnik.nazwisko;
END;
$$
LANGUAGE 'plpgsql';
Poniżej przedstawione zostały konstrukcję pętli LOOP, WHILE i FOR w ramach języka PL/pgSQL.
- Realizacja pętli z kostrukcją LOOP
DO $$
DECLARE
i INTEGER := 0;
BEGIN
LOOP
EXIT WHEN i>9;
i := i + 1;
RAISE NOTICE 'i: %',i;
END LOOP;
END; $$ ;
- Realizacja pętli z konstrukcją WHILE
DO $$
DECLARE
i INTEGER := 0;
BEGIN
WHILE i < 10 LOOP
i := i + 1;
RAISE NOTICE 'i: %',i;
END LOOP;
END; $$;
- Realizacja pętli z konstrukcją FOR
DO $$
BEGIN
FOR i IN 1..10 LOOP
RAISE NOTICE 'i: %',i;
END LOOP;
END; $$;
Przykłady wykorzystania pętli w bazie danych KURS.
- Lista uczestników wyszukiwanych atrybutem LIKE.
Typ zwracanych danych TABLE.
-
CREATE OR REPLACE FUNCTION fun3 (p_pattern VARCHAR)
RETURNS TABLE ( im VARCHAR, naz VARCHAR ) AS -- zwracany typ danych typu tablica
$$
BEGIN
RETURN QUERY
SELECT imie, nazwisko FROM uczestnik WHERE nazwisko LIKE p_pattern ;
END;
$$ LANGUAGE 'plpgsql';
SELECT * FROM fun3('F%');
- Modyfikacja danych, pętla LOOP.
-
CREATE OR REPLACE FUNCTION fun3a (p_pattern VARCHAR)
RETURNS TABLE ( im VARCHAR, naz VARCHAR ) AS
$$
DECLARE
var_r RECORD;
BEGIN
FOR var_r IN (SELECT imie, nazwisko FROM uczestnik
WHERE nazwisko LIKE p_pattern )
LOOP
im := var_r.imie ;
naz := upper(var_r.nazwisko);
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
SELECT * FROM fun3a('F%');
- Uczestnicy kursu, wybór sortowanie { 'U' - nazwisko, grupa, 'G' - grupa, nazwisko },
ustwienie liczby zwróconych wierszy. Wykorzystanie konstrukcji dynamicznej realizacji zapytań w ramach struktury
konstrukcji EXECUTE ... USING.
-
CREATE OR REPLACE FUNCTION fun3b ( sort_type char(1), n INTEGER )
RETURNS TABLE ( im VARCHAR, naz VARCHAR, opis VARCHAR ) AS
$$
DECLARE
rec RECORD;
query text;
BEGIN
query := 'SELECT u.imie, u.nazwisko, ko.opis FROM uczestnik u JOIN uczest_kurs uk ON u.id_uczestnik=uk.id_uczest
JOIN kurs k USING ( id_kurs )
JOIN kurs_opis ko ON k.id_nazwa = ko.id_kurs ';
IF sort_type = 'U' THEN
query := query || 'ORDER BY u.nazwisko, ko.opis ';
ELSIF sort_type = 'G' THEN
query := query || 'ORDER BY ko.opis, u.nazwisko ';
ELSE
RAISE EXCEPTION 'Niepoprawny typ sortowania %s', sort_type;
END IF;
query := query || ' LIMIT $1';
FOR rec IN EXECUTE query USING n
LOOP
-- RAISE NOTICE '% - %', rec.release_year, rec.title;
im := rec.imie ;
naz := rec.nazwisko ;
opis := rec.opis ;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
