Procedury składowane w PostgreSQL/Zwracanie wyników

Procedury mogą zwracać albo pojedyncze wiersze albo ciągi wierszy.

Najczęściej typ wiersza wprost podawany jest po słowie RETURNS. Rzadziej struktura rekordu może być określona ciągiem parametrów OUT i INOUT, wówczas niejawnie typem zwracanym jest polimorficzny record.

Jeśli funkcja ma zwracać ciągi wierszy, typ nazwy musi zostać poprzedzony słówkiem SETOF, czyli RETURNS SETOF typ. Dla rekordów polimorficznych trzeba dodać RETURNS SETOF record.

Od wersji 9.0 można podać strukturę ciągu wierszy używając składni RETURNS TABLE(lista kolumn), gdzie lista kolumn zawiera nazwy i typy kolumn.

Przykłady deklaracji:

-- zawsze zwraca wartość NULL
CREATE FUNCTION foo() RETURNS void AS ...

-- zwraca liczbę
CREATE FUNCTION foo() RETURNS integer AS ...;

-- zwraca jeden rekord z tabeli (tabela jest tutaj nazwą typu!)
CREATE FUNCTION foo() RETURNS tabela AS ...;

-- zwraca jeden rekord o podanej strukturze (bez RETURNS)
CREATE FUNCTION foo(OUT imie text, OUT wiek integer) AS ...;

-- zwraca ciąg liczb
CREATE FUNCTION foo() RETURNS SETOF integer AS ...;

-- zwraca ciąg rekordów z tabeli (tabela jest tutaj nazwą typu!)
CREATE FUNCTION foo() RETURNS SETOF tabela AS ...;

-- zwraca ciąg rekordów o określonej strukturze (z RETURNS)
CREATE FUNCTION foo(OUT imie text, OUT wiek integer) RETURNS SETOF record AS ...;

-- alternatywna forma
CREATE FUNCTION foo() RETURNS TABLE(imie text, wiek integer) AS ...;

Zakończenie procedury umożliwia instrukcja RETURN.

Jeśli procedura zwraca pojedynczy wiersz, wówczas parametrem instrukcji RETURN jest wartość wynikowa - wyrażenie, którego typ musi pasować lub dać się skonwertować do zwracanego typu. Wyjątkiem są procedury void, dla których instrukcja jest bezparametrowa (nie jest nawet wymagana).

Dla procedur zwracających ciągi rekordów instrukcja bezparametrowa powoduje zakończenie procedury. Oprócz tego dostępne są dwa warianty:

• RETURN NEXT rekord - zwraca pojedynczy rekord wyniku;
• RETURN QUERY zapytanie - zwraca ciąg rekordów z zapytania;

Rekordy zwracane muszą odpowiadać typowi wynikowemu procedury.

Zwracanie pojedynczego rekordu

Przy okazji omawiania argumentów funkcji powiedziano, że ciąg argumentów wyjściowych (oznaczanych przez OUT lub INOUT) definiuje typ zwracanej wartości. Gdy jest tylko jeden argument tego rodzaju, wówczas przejmowany jest jego typ. Jeśli jest ich więcej, wówczas definiują typ rekordowy, który posiada takie same pola i w takiej kolejności jak argumenty wyjściowe.

Gdy występują zmienne tego rodzaju, deklaracja funkcji wygląda następująco:

CREATE funkcja(IN x typ, OUT y1 typ1, OUT y2 typ2, ...) AS 'treść' LANGUAGE 'plpgsql';

Wówczas w ciele procedury można odwoływać się bezpośrednio do zmiennych y1, y2 - są one traktowane jako pola rekordu, który jest następnie zwracany jako wynik. Argumenty wyjściowe są inicjowane na NULL, argumenty wejściowo-wyjściowe (INOUT) są inicjowane przez użytkownika, na podstawie parametrów wywołania.

Jeśli w deklaracji funkcji nie ma nie ma argumentów wyjściowych, musi zostać podany jego typ po słówku RETURNS:

CREATE funkcja(IN x typ, ...) RETURN typ AS 'treść' LANGUAGE 'plpgsql';

W przypadku, gdy ma zostać zwrócony typ rekordowy należy go albo uprzednio utworzyć (CREATE TYPE lub CREATE TABLE).

Żeby zilustrować różnice:

-- 1.
CREATE OR REPLACE FUNCTION zwierzaczki1(OUT gatunek text, OUT liczba integer) AS $$
	BEGIN
		gatunek	:= 'kotek';
		liczba	:= 3;
		RETURN;	-- można pominąć
	END;
$$ LANGUAGE 'plpgsql';

SELECT zwierzaczki1();
-- wynik: (kotek,3)

-- 2.
CREATE TYPE zwierzaczek_typ AS (gatunek text, liczba integer);

CREATE OR REPLACE FUNCTION zwierzaczki2(OUT zwierzaczek_typ) AS $$
	BEGIN
		$1.gatunek	:= 'kotek';
		$1.liczba	:= 3;
		RETURN;	-- można pominąć
	END;
$$ LANGUAGE 'plpgsql';

SELECT zwierzaczki2();
-- wynik: (kotek,3)

-- 3.
CREATE OR REPLACE FUNCTION zwierzaczki3() RETURNS zwierzaczek_typ AS $$
	DECLARE
		wynik	zwierzaczek_typ;
	BEGIN
		wynik.gatunek	:= 'kotek';
		wynik.liczba	:= 3;
		RETURN wynik;	-- MUSI wystąpić
	END;
$$ LANGUAGE 'plpgsql';

SELECT zwierzaczki3();
-- wynik: (kotek,3)

Zwracanie wielu rekordów

Aby funkcja mogła zwracać ciąg wierszy, w definicji typ wynikowy musi zostać poprzedzony słówkiem SETOF, np.

CREATE OR REPLACE FUNCTION liczby_parzyste() RETURNS SETOF integer AS ...

Od wersji PostgreSQL 9.0 jako typ można podać definicję rekordu w postaci TABLE(nazwa1 typ1, nazwa2 typ2, ...) - czyli dokładnie w takiej samej formie jak przy tworzeniu nowej tabeli.

CREATE OR REPLACE FUNCTION czestosci_slow() RETURNS TABLE(slowo text, liczba_wystapien integer) AS ...

To rozwiązanie ogranicza nieco możliwości, bowiem nie będzie można łatwo zadeklarować zmiennej takiego typu.

Funkcja może zwracać:

• pojedyncze wiersze instrukcją RETURN NEXT;
• wyniki zapytań statycznych instrukcją RETURN QUERY zapytanie;
• wyniki zapytań dynamicznych instrukcją RETURN QUERY EXECUTE treść zapytania [USING lista parametrów]

Instrukcje te można dowolnie ze sobą mieszać.

UWAGA! W obecnej chwili (9.0), w przypadku instrukcji RETURN NEXT, przekazywane przez nią rekordy są zbierane do chwili zakończenia procedury i dopiero wtedy zwracane w całości. Przy dużych ilościach danych może to stanowić problem.

Przykład

Procedura zwraca dwa pojedyncze rekordy, oraz wynik zapytania. (Akurat w tym przypadku podobny efekt można by uzyskać za pomocą 3 zapytań połączonych UNION).

CREATE TABLE studenci (
    imie        text,
    nazwisko    text,
    ocena       numeric(2,1)
);

CREATE OR REPLACE FUNCTION wyniki_egzaminu() RETURNS SETOF studenci AS $$
    DECLARE
        stud    studenci%ROWTYPE;
    BEGIN
        -- pierwszy rekord
        stud.imie     := 'Max';
        stud.nazwisko := 'Debeściak';
        stud.ocena    := 6.0;
        RETURN NEXT stud;

        -- wiersze za zapytania
        RETURN QUERY
            SELECT * FROM studenci ORDER BY nazwisko, imie;

        -- ostatni rekord
        stud.imie     := 'Zero';
        stud.nazwisko := 'Niemożliwe';
        stud.ocena    := 2.0;
        RETURN NEXT stud;

        RETURN; -- koniec procedury, można pominąć
    END;
$$ LANGUAGE 'plpgsql';

INSERT INTO studenci (imie, nazwisko, ocena) VALUES
    ('Jan', 'Kowalski', 4.5),
    ('Tomasz', 'Nowak', 3.0),
    ('Anna', 'Wysocka', 5.0),
    ('Katarzyna', 'Kot', 4.0)
;

SELECT * FROM wyniki_egzaminu();

   imie    |  nazwisko  | ocena 
-----------+------------+-------
 Max       | Debeściak  |   6.0
 Katarzyna | Kot        |   4.0
 Jan       | Kowalski   |   4.5
 Tomasz    | Nowak      |   3.0
 Anna      | Wysocka    |   5.0
 Zero      | Niemożliwe |   2.0
(6 rows)

Zobacz też

• Czy wywoływać procedurę, gdy jeden z argumentów jest NULL?