Wywoływanie funkcji z zapytań SQL

Wyobraź sobie, że masz funkcję do skomplikowanych obliczeń albo przetwarzania danych. Bez możliwości integracji funkcji w zapytaniach SQL pracowałbyś tak:

1. Wywołujesz funkcję z jakiegoś języka programowania (np. Python albo JavaScript).
2. Przekazujesz wynik do zapytania SQL.

To zbędny krok! W PostgreSQL możesz wbudować funkcje bezpośrednio w zapytanie SQL, skracając kod, przyspieszając operacje i zmniejszając liczbę wywołań z serwera. To szczególnie przydatne do:

• Automatyzacji obliczeń.
• Walidacji danych przed wstawieniem.
• Modyfikacji istniejących danych.

Wywoływanie funkcji w SELECT

Zacznijmy od podstaw i zobaczmy, jak używać funkcji w zwykłym zapytaniu SELECT. Załóżmy, że mamy tabelę students z informacjami o studentach. Chcemy napisać funkcję, która zwraca aktualny wiek studenta na podstawie daty jego urodzenia.

Krok 1: pisanie funkcji

Stwórzmy funkcję calculate_age, która przyjmuje datę urodzenia i zwraca wiek:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Krok 2: użycie funkcji w zapytaniu SELECT

Teraz możemy wywołać tę funkcję dla każdego rekordu z tabeli:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

Co się dzieje?

• Dla każdego wiersza z tabeli students funkcja calculate_age oblicza wiek.
• Zwrócona wartość pojawia się w kolumnie age.

Przykład wyniku:

Jak widzisz, nic trudnego, a wynik wygląda fajnie i profesjonalnie.

Wywoływanie funkcji w INSERT

Funkcje są też przydatne przy wstawianiu danych. Na przykład, załóżmy, że mamy tabelę logs, gdzie zapisujemy akcje użytkowników. Chcemy wstawiać log-message, używając funkcji do automatycznego generowania tekstu.

Krok 1: tworzenie funkcji

Napiszmy funkcję generate_log_message, która przyjmuje nazwę użytkownika i akcję, a zwraca tekst wiadomości:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

Krok 2: użycie funkcji w INSERT

Teraz wstawiamy wiadomość do tabeli logs, wywołując funkcję przy dodawaniu wiersza:

INSERT INTO logs (message)
VALUES (generate_log_message('Otto', 'logowanie na stronę'));

Wynik:

Funkcja robi wszystko za nas: dba o formatowanie tekstu i dodaje znacznik czasu. To świetny przykład, jak funkcje mogą automatyzować nudne czynności.

Wywoływanie funkcji w UPDATE

Funkcje mogą być używane także do modyfikacji danych w tabeli. Załóżmy, że mamy tabelę students i chcemy zaktualizować nazwę grupy, używając funkcji do przeniesienia ich na nowy rok.

Krok 1: tworzenie funkcji

Napiszmy funkcję promote_student, która przyjmuje starą grupę (np. 101) i zwraca nową (np. 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Krok 2: użycie funkcji w UPDATE

Aktualizujemy wszystkim studentom ich grupę:

UPDATE students
SET group_name = promote_student(group_name);

Wynik:

Zobacz, jak wywoływana funkcja robi magię aktualizacji: stare grupy zamieniają się w nowe ciągi znaków.

Wywoływanie funkcji w warunkach WHERE

Funkcje mogą być używane w warunkach filtrowania. Rozwińmy nasz przykład z wiekiem studentów.

Krok 1: filtrowanie po wieku

Użyjmy już stworzonej funkcji calculate_age, żeby wybrać studentów starszych niż 20 lat:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Wynik:

Tutaj cała robota spada na funkcję, która liczy wiek każdego studenta na bieżąco.

Łączenie z funkcjami agregującymi

Trochę utrudnijmy zadanie. Musimy policzyć łączną liczbę studentów młodszych niż 22 lata. Funkcje świetnie współpracują z funkcjami agregującymi, jak COUNT().

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

Co się dzieje?

• Funkcja calculate_age jest używana w filtrze.
• COUNT(*) liczy liczbę wierszy spełniających warunek.

Prawdziwe przykłady użycia

Automatyzacja walidacji danych. Załóżmy, że chcesz sprawdzić, czy wiek wszystkich studentów w tabeli mieści się w typowym zakresie (np. od 18 do 30 lat). Napisz funkcję do sprawdzania i użyj jej w warunku WHERE.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Optymalizacja wstawiania danych. Wyobraź sobie, że pracujesz w sklepie internetowym. Zamiast liczyć całkowitą cenę zamówienia po stronie klienta, napisz funkcję, która policzy ją od razu przy dodawaniu danych do tabeli orders.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Typowe błędy przy wywoływaniu funkcji

Kiedy zaczniesz aktywnie używać funkcji w zapytaniach, mogą pojawić się błędy. Oto kilka typowych sytuacji i jak sobie z nimi radzić:

Brak wymaganych uprawnień. Jeśli nie jesteś właścicielem funkcji albo tabeli, PostgreSQL może zablokować wywołanie. Upewnij się, że masz prawa do wykonania funkcji.

Niezgodność typów. Przy przekazywaniu argumentów do funkcji zwracaj uwagę na typy danych. Na przykład, jeśli funkcja oczekuje DATE, a podasz stringa, dostaniesz błąd. Użyj jawnej konwersji:

SELECT calculate_age('2000-01-01'::DATE);

Błędy składni w funkcjach. Jeśli funkcja zwraca błąd, może to rozwalić całe zapytanie. Testuj dokładnie funkcje przed użyciem.