Tworzenie prostych funkcji: CREATE FUNCTION

W PostgreSQL funkcje to potężne narzędzie, które pozwala automatyzować zadania, tworzyć logikę biznesową i dawać serwerowi więcej inteligencji. Wyobraź sobie funkcje jako mini-programy, które wykonują się wewnątrz bazy danych. Są wygodne do:

• Ponownego użycia kodu. Jeśli powtarzasz te same zapytania kilka razy, wrzuć je do funkcji i wywołuj, kiedy chcesz.
• Automatyzacji zadań. Na przykład, musisz policzyć pensję pracowników na podstawie ich godzin pracy. Funkcja ogarnie to bez problemu.
• Enkapsulacji logiki. Pozwala zostawić skomplikowane obliczenia po stronie serwera, żeby klienci nie musieli się głowić nad zapytaniami SQL.

Ogólna składnia CREATE FUNCTION

Tak wygląda ogólna struktura tworzenia funkcji:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Ciało funkcji (logika)
    RETURN wynik;
END;
$$ LANGUAGE plpgsql;

Rozbijmy to na części:

CREATE FUNCTION function_name(parameters):

W tej linijce podajesz nazwę funkcji function_name i określasz parametry (jeśli są potrzebne).

Parametry mogą mieć nazwę i typ danych: my_param INTEGER, another_param TEXT.

RETURNS return_type:

Określasz, co funkcja zwraca: jedną wartość (INTEGER, TEXT itd.) albo zestaw danych (TABLE, RECORD).

BEGIN ... END:

Między tymi słowami kluczowymi jest "ciało" funkcji, czyli cała magia.

RETURN wynik:

Zwraca wynik działania funkcji. Uważaj: typ wyniku musi się zgadzać z tym, co podałeś w RETURNS.

LANGUAGE plpgsql:

Podajesz, że używasz języka PL/pgSQL. PostgreSQL obsługuje też inne języki, ale teraz skupiamy się na tym.

Prosty przykład: dodawanie dwóch liczb

Stwórzmy funkcję, która zwraca sumę dwóch liczb całkowitych.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Teraz ją wywołamy:

SELECT add_numbers(5, 7); -- Wynik: 12

Co tu się dzieje?

• Funkcja przyjmuje dwa parametry a i b typu INT.
• W środku funkcji po prostu je dodajemy (a + b) i zwracamy wynik.
• Proste jak kalkulator!

Przykład z użyciem zmiennych

Załóżmy, że mamy bazę danych uniwersytetu i chcemy wiedzieć, ilu studentów jest zarejestrowanych.

Stwórzmy funkcję:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Deklarujemy zmienną do przechowania wyniku
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Liczymy ilość wierszy w tabeli
    RETURN total; -- Zwracamy wynik
END;
$$ LANGUAGE plpgsql;

Wywołanie funkcji:

SELECT count_students(); -- Załóżmy, wynik: 120

Widzimy tutaj:

• Użycie zmiennej total do przechowania wyniku zapytania SQL.
• Komenda SELECT ... INTO zapisuje wynik zapytania do zmiennej.

To podejście jest szczególnie wygodne, jeśli musisz najpierw przetworzyć dane, a potem je zwrócić.

Zwracanie wielu wartości: RETURNS TABLE

W poprzednim przykładzie zwracaliśmy tylko jedną wartość. Ale co jeśli funkcja ma zwrócić zestaw danych, np. listę studentów? Właśnie tutaj RETURNS TABLE się przydaje.

Przykład:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Wywołanie funkcji:

SELECT * FROM get_students();

Możliwy wynik:

Przydatność RETURN QUERY do wykonywania zapytań w funkcji

RETURN QUERY pozwala nam zwrócić wynik zapytania SQL bezpośrednio z funkcji. To skraca kroki pośrednie i upraszcza funkcje.

Stwórzmy funkcję, która zwraca tylko tych studentów, którzy są aktywni:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Zanim wywołasz funkcję get_active_students(), musisz stworzyć tabelę students i wrzucić do niej testowe dane. Tak to można zrobić:

-- Tworzymy tabelę studentów
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- Dodajemy kilka rekordów
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Tabela:

Teraz wywołanie:

SELECT * FROM get_active_students();

Wynik:

Sprawdzanie poprawności danych przed wykonaniem

Funkcje mogą zawierać sprawdzenia IF, żeby mieć pewność, że dane są poprawne. Na przykład możemy stworzyć funkcję do przeniesienia studenta na wyższy rok tylko jeśli zdał wszystkie egzaminy.

Przykład:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- Liczymy ilość zdanych egzaminów studenta
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'passed';

    -- Sprawdzamy warunek
    IF passed_exams < 5 THEN
        RETURN 'Student nie zdał wystarczającej liczby egzaminów';
    END IF;

    -- Aktualizujemy rok studenta
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN 'Student awansował!';
END;
$$ LANGUAGE plpgsql;

Typowe błędy przy tworzeniu funkcji

Brak typu wyniku. PostgreSQL zawsze wymaga, żebyś określił, jaki wynik zwraca funkcja. Na przykład:

CREATE FUNCTION fail() AS $$ -- Błąd: brak RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Poprawka:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Niezgodność typu wyniku. Jeśli podajesz RETURNS INT, musisz zwrócić liczbę. Próba zwrócenia tekstu w tym przypadku to zły pomysł.

Błąd w zapytaniach SQL w funkcji. Zawsze sprawdzaj zapytania przed użyciem w funkcjach. Najlepiej testować je "ręcznie" przez psql albo pgAdmin.