Podstawowe możliwości PL/pgSQL

No to wchodzimy głębiej w to, co sprawia, że PL/pgSQL jest takim mocnym i niezastąpionym narzędziem dla devów i adminów baz danych. Na tym wykładzie pogadamy o zaletach PL/pgSQL, jego unikalnych ficzerach i zobaczymy przykłady, które pokażą, jak te możliwości mogą się przydać w codziennej pracy.

Żeby ogarnąć, po co nam PL/pgSQL, wyobraź sobie świat, gdzie każde zadanie programistyczne trzeba ogarnąć tylko za pomocą SQL. Na przykład, żeby policzyć liczbę studentów na każdym wydziale, musiałbyś napisać złożone zapytanie SQL, a potem obrabiać wyniki po stronie klienta. Mało efektywne, nie? I tu właśnie wjeżdża PL/pgSQL z obsługą zmiennych, pętli, warunków i obsługą błędów.

Zalety używania PL/pgSQL:

1. Logika po stronie serwera: PL/pgSQL pozwala zmniejszyć ilość danych przesyłanych między serwerem a klientem, bo cała logika leci na serwerze. To zmniejsza lagi sieciowe.
2. Wydajność: funkcje w PL/pgSQL są kompilowane i trzymane w bazie, więc działają szybciej niż seria pojedynczych zapytań SQL.
3. Automatyzacja zadań: z PL/pgSQL możesz automatyzować nudne operacje, jak update danych, logowanie czy sprawdzanie spójności info.
4. Logika biznesowa: PL/pgSQL pozwala ogarnąć skomplikowaną logikę biznesową, jak obliczenia, walidacje czy generowanie raportów analitycznych.
5. Wygoda i czytelność: kod w PL/pgSQL łatwo podzielić na funkcje i ogarnąć, więc jest wygodny w utrzymaniu.

Gdzie używać PL/pgSQL

No to zobaczmy, gdzie dokładnie można użyć PL/pgSQL i jak rozwiązuje realne problemy.

1. Automatyzacja powtarzalnych operacji

PL/pgSQL pozwala automatyzować powtarzające się zadania. Na przykład, musisz codziennie aktualizować jakieś dane albo cyklicznie odpalać analizę. Tworzysz funkcję w PL/pgSQL i łatwo podłączasz ją do scheduler’a (np. pg_cron), żeby odpalała się o określonej godzinie.

Przykład: automatyczna aktualizacja statusu

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = 'nieaktywny'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE 'Statusy studentów zaktualizowane.';
END;
$$ LANGUAGE plpgsql;

Ta funkcja automatycznie ustawia status "nieaktywny" dla studentów, którzy nie logowali się do systemu przez ponad rok.

2. Generowanie raportów

PL/pgSQL świetnie nadaje się do tworzenia raportów analitycznych, gdzie trzeba agregować i łączyć dane z kilku tabel. Możesz napisać procedury do automatycznego generowania raportów i zapisywania ich do osobnych tabel.

Przykład: raport liczby studentów na wydziałach

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

Po wywołaniu tej funkcji dostaniesz statystyki dla wszystkich wydziałów.

3. Logowanie zmian w tabelach

Logowanie — to proces zapisywania zmian danych w tabelach bazy. PL/pgSQL pozwala ogarnąć to efektywnie, np. przez triggery.

Przykład funkcji do logowania zmian

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ta funkcja wrzuca do tabeli change_logs info o tym, która tabela została zmieniona, jaka operacja była wykonana (np. INSERT, UPDATE, DELETE), a także zapisuje stare i nowe dane.

4. Implementacja złożonych algorytmów

Z PL/pgSQL możesz programować algorytmy, które wykraczają poza możliwości zwykłego SQL. Na przykład, mogą to być obliczenia kosztów, sprawdzanie reguł biznesowych czy automatyczne generowanie identyfikatorów.

Przykład: generowanie unikalnego identyfikatora

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

Ta funkcja tworzy unikalny identyfikator, dodając aktualny znacznik czasu i losową liczbę.

5. Praca z triggerami

Triggery i PL/pgSQL to duet idealny. Gdy musisz zautomatyzować jakieś zadanie, np. update powiązanych danych, triggery z funkcjami PL/pgSQL są super narzędziem.

Przykład: trigger na usuwanie studentów

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE 'Zapis studentów % usunięty.', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Korzystając z tej funkcji, możesz automatycznie usuwać wpisy o zapisach studentów z tabeli enrollments, jeśli student zostanie usunięty z tabeli students.

6. Obsługa błędów

Przy trudniejszych zadaniach obsługa błędów jest mega ważna. PL/pgSQL daje blok EXCEPTION, który pozwala łapać i obsługiwać błędy.

Przykład: obsługa błędów

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE 'Id wydziału % nie istnieje!', faculty_id;
END;
$$ LANGUAGE plpgsql;

Tu, jeśli pojawi się błąd z wpisaniem nieistniejącego wydziału, pojawi się ostrzeżenie zamiast crasha.

Przykłady trudniejszych zadań rozwiązywanych z PL/pgSQL

Żeby Cię zainspirować do używania PL/pgSQL, oto kilka przykładów zadań, które pomaga ogarnąć:

1. Automatyczna aktualizacja zniżek w sklepie internetowym Funkcja, która codziennie aktualizuje zniżki dla produktów, którym kończy się promocja.

2. Sprawdzanie i naprawa danych Funkcja, która sprawdza tabelę pod kątem duplikatów i je usuwa.

3. Szybkie przełączanie konfiguracji Funkcja, która pozwala zmienić parametry systemu, np. przełączyć tryb działania aplikacji.

Prawdziwe przykłady ze świata IT

PL/pgSQL używają miliony firm na całym świecie. Na przykład:

• Sklepy internetowe korzystają z funkcji do obliczania podatków, automatycznej aktualizacji zniżek i generowania raportów sprzedaży.
• Banki używają PL/pgSQL do obsługi tysięcy operacji dziennie, od naliczania odsetek po sprawdzanie scoringu kredytowego.
• Portale społecznościowe wdrażają skomplikowane algorytmy przetwarzania danych, np. do polecania znajomych.

PL/pgSQL to taki szwajcarski scyzoryk dla programistów pracujących z PostgreSQL. Nie tylko ułatwia pracę z bazą, ale pozwala ogarnąć zadania, które w zwykłym SQL byłyby mega trudne albo wręcz niemożliwe. I co najważniejsze — PL/pgSQL łatwo się nauczyć, a z nim każdy może poczuć się jak prawdziwy master baz danych.