Operatory RAISE NOTICE, RETURN QUERY

W programowaniu liczą się dwie rzeczy: ogarniać, co się dzieje (szczególnie gdy wszystko idzie nie tak, jak planowałeś), i zwracać przydatne dane. To szczególnie ważne w PL/pgSQL, bo wszystko dzieje się po stronie serwera, więc debugowanie nie zawsze jest proste. Na szczęście mamy wbudowane narzędzia:

RAISE NOTICE — to sposób na wyświetlanie komunikatów podczas działania funkcji. Wyobraź sobie to jak console.log w JavaScript albo print w Pythonie. Możesz pokazać wartości zmiennych, aktualny stan wykonania albo po prostu zostawić "powitanie" dla siebie z przyszłości.

RETURN QUERY — to sposób na zwrócenie zestawu danych, np. całej tabeli albo wyniku złożonego zapytania. Dzięki temu funkcje PL/pgSQL stają się podobne do pełnoprawnych zapytań SQL.

Komenda RAISE NOTICE

RAISE NOTICE pozwala wyświetlać komunikaty na ekranie w trakcie działania funkcji. Format wygląda tak:

RAISE NOTICE 'Komunikat: %', wartosc;

• % — to placeholder dla zmiennej, podobny do printf w C.
• Po tekście komunikatu możesz wymienić zmienne, które mają się pojawić.

Przykład użycia

Wyobraź sobie, że piszesz funkcję, która liczy ilu studentów jest w różnych grupach. Chcesz widzieć wartości pośrednie, żeby ogarnąć, czy wszystko idzie zgodnie z planem.

CREATE OR REPLACE FUNCTION count_students_in_groups() RETURNS VOID AS $$
DECLARE
    group_name TEXT;
    student_count INT;
BEGIN
    FOR group_name IN SELECT DISTINCT group_name FROM students LOOP
        SELECT COUNT(*) INTO student_count
        FROM students WHERE group_name = group_name;

        -- Wyświetlanie wyników
        RAISE NOTICE 'Grupa: %, Liczba studentów: %', group_name, student_count;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Kiedy wywołasz tę funkcję:

SELECT count_students_in_groups();

Zobaczysz takie komunikaty w logach:

NOTICE:  Grupa: Matematyka, Liczba studentów: 30
NOTICE:  Grupa: Filozofia, Liczba studentów: 25
NOTICE:  Grupa: Biologia, Liczba studentów: 18

Zwróć uwagę, że funkcja nic nie zwraca (jest stworzona z RETURNS VOID), ale RAISE NOTICE pokazuje nam, jak idzie pętla.

Przydatne triki z RAISE

Oprócz NOTICE możesz użyć też innych poziomów komunikatów:

• RAISE DEBUG — dla dodatkowych informacji (pokazuje się tylko, jeśli poziom logowania to DEBUG).
• RAISE INFO — dla ogólnych informacji.
• RAISE WARNING — dla ostrzeżeń.
• RAISE EXCEPTION — do generowania błędów, o czym pogadamy później.

Do debugowania najlepiej używać NOTICE albo DEBUG, bo są wygodne i nie zatrzymują działania funkcji.

Komenda RETURN QUERY: zwracaj dane jak pro

RETURN QUERY używa się w PL/pgSQL do zwracania zestawu wierszy. Dzięki temu możemy zwrócić wynik zapytania SQL prosto z funkcji. Składnia wygląda tak:

RETURN QUERY <zapytanie SQL>;

Możesz też połączyć kilka zapytań:

RETURN QUERY <zapytanie SQL 1>;
RETURN QUERY <zapytanie SQL 2>;

Przykład 1: funkcja z RETURN QUERY

Napiszemy funkcję, która zwraca listę studentów z podanej grupy.

CREATE OR REPLACE FUNCTION get_students_by_group(group_name TEXT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name 
    FROM students
    WHERE group_name = group_name;
END;
$$ LANGUAGE plpgsql;

Teraz wywołajmy tę funkcję:

SELECT * FROM get_students_by_group('Matematyka');

Żeby sprawdzić działanie funkcji, najpierw stwórz tabelę students i dodaj do niej dane:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
group_name TEXT NOT NULL
);

INSERT INTO students (name, group_name) VALUES
('Otto Song',     'Fizyka'),
('Alex Lin',      'Matematyka'),
('Anna Vel',      'Matematyka'),
('Maria Chi',     'Historia');

Wynik:

Jak widzisz, funkcja działa jak zwykłe zapytanie SQL.

Przykład 2: Łączenie kilku zapytań

A co jeśli chcemy zwrócić połączone dane z kilku tabel? Zwróćmy listę studentów i kursów, na które są zapisani.

CREATE OR REPLACE FUNCTION get_students_and_courses()
RETURNS TABLE(student_name TEXT, course_name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT s.name, c.name
    FROM students s
    JOIN enrollments e ON s.id = e.student_id
    JOIN courses c ON e.course_id = c.id;
END;
$$ LANGUAGE plpgsql;

Najpierw stwórzmy trzy tabele: students, courses i enrollments, a potem dodajmy trochę danych:

-- Tabela studentów
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

-- Tabela kursów
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

-- Tabela zapisów na kursy (łącząca)
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id)
);

-- Dodaj studentów
INSERT INTO students (name) VALUES
('Otto Song'),
('Alex Lin'),
('Anna Vel'),
('Maria Chi');

-- Dodaj kursy
INSERT INTO courses (name) VALUES
('Matematyka'),
('Fizyka'),
('Historia');

-- Dodaj zapisy na kursy
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 2), -- Otto -> Fizyka
(2, 1), -- Alex -> Matematyka
(3, 1), -- Anna -> Matematyka
(4, 3); -- Maria -> Historia

W tym przypadku wywołanie funkcji:

SELECT * FROM get_students_and_courses();

da taki wynik:

Funkcja ładnie łączy dane z trzech tabel i pokazuje, który student jest zapisany na jaki kurs.

Łączenie RAISE NOTICE i RETURN QUERY

Czasem RETURN QUERY i RAISE NOTICE mogą działać razem w jednej funkcji, żebyś mógł kontrolować wykonanie funkcji i widzieć wyniki pośrednie.

Oto przykład funkcji, która zwraca dane o studentach i jednocześnie wyświetla komunikaty, żeby pokazać postęp działania:

CREATE OR REPLACE FUNCTION debug_students()
RETURNS TABLE(student_id INT, student_name TEXT) AS $$
DECLARE
    count_students INT;
BEGIN
    -- Liczymy ilu jest studentów
    SELECT COUNT(*) INTO count_students FROM students;
    RAISE NOTICE 'Łącznie studentów: %', count_students;

    -- Zwracamy dane o studentach
    RETURN QUERY
    SELECT id, name FROM students;

    RAISE NOTICE 'Funkcja zakończyła działanie.';
END;
$$ LANGUAGE plpgsql;

Jeśli tabela students jeszcze nie istnieje, dodaj ją i wrzuć dane:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

INSERT INTO students (name) VALUES
('Otto Song'),
('Alex Lin'),
('Anna Vel'),
('Maria Chi');

Teraz, wywołując funkcję:

SELECT * FROM debug_students();

Dostaniesz w wyniku zarówno dane, jak i komunikaty:

Wyświetlanie komunikatów w konsoli:

NOTICE:  Łącznie studentów: 4
NOTICE:  Funkcja zakończyła działanie.

Typowe błędy przy użyciu

Błąd ze zmiennymi w RAISE NOTICE: jeśli zapomnisz zadeklarować zmienną albo zrobisz literówkę w jej nazwie, dostaniesz błąd variable does not exist. Zawsze sprawdzaj, czy zmienne są poprawnie zadeklarowane.

Błąd z typem zwracanym: jeśli używasz RETURN QUERY, ale nie podasz RETURNS TABLE przy tworzeniu funkcji, PostgreSQL wyrzuci błąd. Upewnij się, że typy zwracane zgadzają się z danymi, które zwracasz.

Błąd z placeholderami w RAISE: jeśli liczba placeholderów % nie zgadza się z liczbą zmiennych, pojawi się błąd. Na przykład:

RAISE NOTICE 'Wartość: %, %', value1;

To wywoła błąd, bo brakuje drugiej zmiennej.