Procedury składowane w PostgreSQL/Język PLpgSQL/Kursory

Typem danych kursora jest REFCURSOR. Podstawowe operacje na kursorach:

• otwieranie (OPEN),
• wczytywanie jednego lub więcej wierszy (FETCH),
• zmiana pozycji (MOVE),
• zamknięcie (CLOSE).

Zobacz też:

• Jak deklarować kursory w PL/pgSQL?
• Jak wczytywać dane z kursorów?

Nazwa kursora

Nazwa kursora jest domyślnie ustawiana na <unnamed cursor n> lub <unnamed portal n>, gdzie n jest liczbą całkowitą wybieraną przez serwer. Nie ma większego znaczenia, jeśli kursor jest używany jedynie wewnątrz procedury składowanej, gdy jednak ma zostać zwrócony w celu wykonywania na nim jakiś instrukcji SQL, sensowna nazwa staje się istotna.

Przypisanie napisu do zmiennej kursorowej ustawia jego nazwę. Należy to zrobić przed otwarciem kursora; można także po, ale dalsze działania mogą stać się niemożliwe.

DECLARE
    kursor REFCURSOR;
BEGIN
    kursor := 'dane';
    OPEN kursor FOR ...
END;

Otwieranie kursorów

Kursor otwierany jest instrukcją OPEN. Składnia dla kursorów ograniczonych:

OPEN kursor [ ( argumenty ) ]

Gdzie kursor jest nazwą zmiennej, a argumenty listą opcjonalnych parametrów. Dla kursorów nieograniczonych składnia jest podobna do tej, używanej przy deklaracji:

OPEN kursor [ NO [ SCROLL ] ] FOR zapytanie
OPEN kursor [ NO [ SCROLL ] ] FOR EXECUTE napis [ USING lista wartości ]

Słowo SCROLL decyduje, że można będzie przesuwać kursor wstecz, np. MOVE PRIOR FROM kursor. Domyślnie jest to zabronione - NO SCROLL.

Zapytanie jest albo zapytaniem statycznym, albo dynamicznym.

Przykład 1 - kursory nieograniczone

DECLARE
    kursor1   REFCURSOR;
    kursor2   REFCURSOR;
BEGIN
    OPEN kursor1 FOR
        SELECT * FROM tabela WHERE id = elem_id;

    ...

    OPEN kursor2 FOR
        EXECUTE 'SELECT * FROM ' || nazwa::regclass || ' WHERE id=$1' USING elem_id;

Przykład 2 - kursory ograniczone

DECLARE
    kursor1 CURSOR FOR SELECT * FROM tabela ORDER BY kolumna;
    kursor2 CURSOR (argument integer) FOR SELECT * FROM tabela WHERE id=argument;
BEGIN
    OPEN kursor1;

    OPEN kursor2(elem_id);

Zamykanie kursorów

Zamknięcie kursora umożliwia instrukcja CLOSE kursor. Kursor musi być już otwarty, inaczej zostanie zgłoszony błąd.

Zwracanie kursorów

Przykład

-- procedura otwiera i zwraca kursor
CREATE OR REPLACE FUNCTION kursor(nazwa_kursora text) RETURNS refcursor AS $$
	DECLARE
		c	refcursor := nazwa_kursora;
	BEGIN
		OPEN c FOR SELECT * FROM pracownicy;
		RETURN c;
	END;
$$ LANGUAGE 'plpgsql';

Użycie:

BEGIN;
SELECT kursor('nasz kursor');

-- uwaga, nazwa kursora to nazwa kolumny, dlatego w cudzysłowie!
FETCH ALL IN "nasz kursor";

COMMIT;

Kursory, jako argumenty funkcji

Funkcja musi przyjmować argument typu REFCURSOR, natomiast kursor musi zostać wcześniej zadeklarowany instrukcją SQL DECLARE. Funkcji musi zostać przekazana nazwa kursora, jako napis.

W ciele procedury można już korzystać z kursora bez ograniczeń.

Przykład

Procedura zwracająca co 4-ty wiersz wyniku:

CREATE OR REPLACE FUNCTION co_czwarty(K REFCURSOR) RETURNS SETOF text AS $$
    DECLARE
        nazwa   text;
    BEGIN
        LOOP
            FETCH NEXT FROM K INTO nazwa;
            EXIT WHEN NOT FOUND;

            RETURN NEXT nazwa;
            MOVE +3 FROM K;
        END LOOP;
    END;
$$ LANGUAGE 'plpgsql';

I przykładowa sesja

-- kursory mogą być tworzone tylko w obrębie transakcji
BEGIN;

-- stworzenie kursora
DECLARE kursor CURSOR FOR SELECT relname FROM pg_class ORDER BY relname;

-- i wywołanie funkcji
SELECT co_czwarty('kursor');

ROLLBACK;