Jak zwracać wartości z funkcji w PL/pgSQL

Kiedy piszesz funkcje w PostgreSQL, jednym z pierwszych pytań jest jak zwrócić wynik. Czasem trzeba po prostu zwrócić jedną liczbę. Czasem — całą tabelę. A czasem nawet kilka zestawów danych. W tej części ogarniemy wszystkie podstawowe opcje: od najprostszego RETURN po RETURN QUERY, RETURNS TABLE i SETOF.

Jeden wynik: RETURN

Jeśli twoja funkcja ma zwracać tylko jedną wartość — na przykład sumę albo liczbę wierszy — użyj zwykłego RETURN.

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Kiedy tworzysz funkcję w PL/pgSQL, musisz określić, co ona zwraca. To dotyczy słowa kluczowego RETURNS, które definiuje "format" wyniku zwracanego przez funkcję. Więc jeśli chcesz zwrócić jedną liczbę, tekst albo tabelę danych — wszystko to trzeba wpisać w linii z RETURNS.

Prosty przykład:

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

Tutaj słowo kluczowe RETURNS INT mówi, że funkcja zwraca liczbę.

Zwracanie jednej wartości

Zacznijmy od najprostszego — funkcji, która zwraca jedną wartość. Na przykład funkcja, która liczy ilu studentów jest w tabeli students:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Zapisujemy wynik zapytania do zmiennej total
    RETURN total; -- Zwracamy wynik
END;
$$ LANGUAGE plpgsql;

Teraz możemy wywołać tę funkcję:

SELECT count_students(); -- Zwróci liczbę studentów

Zwracanie wielu wartości za pomocą RETURNS TABLE

Czasem trzeba zwrócić nie jedną wartość, a cały zestaw rekordów. Na przykład listę wszystkich studentów z ich imionami i identyfikatorami. Do tego służy konstrukcja RETURNS TABLE.

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students; -- Zwracamy wynik zapytania jako tabelę
END;
$$ LANGUAGE plpgsql;

Teraz możemy wywołać tę funkcję:

SELECT * FROM get_students(); -- Zwróci tabelę ze wszystkimi studentami

Zwróć uwagę na słowa kluczowe RETURNS TABLE. One mówią, że funkcja zwraca tabelę z podanymi kolumnami (id i name w tym przypadku).

Użycie RETURN QUERY

Pewnie już spodobał ci się nasz przykład wyżej. Ale jest jeszcze jeden bajer: RETURN QUERY — to taka magiczna różdżka PL/pgSQL, która pozwala zwracać dane bezpośrednio z zapytania. Dzięki niej możesz zwrócić wynik całego zapytania albo jego podzbiór.

Załóżmy, że chcemy zwrócić wszystkich studentów, którzy aktywnie się uczą (ich status w bazie to active = TRUE):

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

Teraz możemy wywołać funkcję i dostać dane o aktywnych studentach:

SELECT * FROM get_active_students();

Zwracanie wielu wierszy bez RETURNS TABLE

Czasem możesz chcieć zwrócić wiersze danych bez użycia RETURNS TABLE. Do tego możesz użyć typu danych SETOF. To pozwala zwracać wiersze o tej samej strukturze. Na przykład:

CREATE FUNCTION get_student_names() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN QUERY
    SELECT name
    FROM students;
END;
$$ LANGUAGE plpgsql;

Ta funkcja zwraca tylko listę imion studentów:

SELECT * FROM get_student_names();

Zwracanie wartości w zależności od danych wejściowych

Funkcje nie zawsze zwracają tylko statyczne wyniki. Mogą używać parametrów, żeby dynamicznie zmieniać wyniki.

Oto przykład zwracania danych po identyfikatorze studenta

CREATE FUNCTION get_student_by_id(student_id INT) RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE id = student_id; -- Używamy parametru student_id
END;
$$ LANGUAGE plpgsql;

Teraz możesz pobrać info o konkretnym studencie:

SELECT * FROM get_student_by_id(3); -- Zwróci dane studenta z ID = 3

Zwracanie złożonych danych (kilka zestawów)

Czasem dane są tak złożone, że trzeba je zwracać w kilku zestawach. Do tego możesz użyć kursora. Na przykład, jeśli chcesz zwrócić dwa zestawy danych z funkcji — listę aktywnych studentów i nieaktywnych studentów.

CREATE FUNCTION get_students_status() RETURNS SETOF RECORD AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, 'active' AS status
    FROM students
    WHERE active = TRUE;

    RETURN QUERY
    SELECT id, name, 'inactive' AS status
    FROM students
    WHERE active = FALSE;
END;
$$ LANGUAGE plpgsql;

Teraz możesz pobrać oba zestawy danych:

SELECT * FROM get_students_status();

Typowe błędy przy pracy z RETURNS

Brak określenia typu zwrotu: Jeśli nie podasz, co funkcja zwraca, PostgreSQL wyrzuci błąd. Na przykład:

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

Niepasujące typy danych: upewnij się, że zwracane wartości pasują do zadeklarowanych typów. Na przykład, jeśli podałeś, że zwracasz INT, nie próbuj zwracać tekstu.

Brak użycia RETURN QUERY: jeśli zapomnisz użyć RETURN QUERY przy złożonym zapytaniu, funkcja po prostu nic nie zwróci.

Niepoprawny zwrot wielu wartości: jeśli zwracasz wiersz danych, ale zapomnisz użyć SETOF albo TABLE, PostgreSQL wyrzuci błąd.