Procedury składowane w PostgreSQL/Język PLpgSQL/Pętle

Etykiety

Każda pętla, podobnie jak blok, może posiadać etykietę. Służy również do identyfikacji pętli w przypadku przerywania lub pomijania iteracji.

Przerywanie i pomijanie iteracji

Przerywanie pętli wykonuje instrukcja EXIT, pominięcie iteracji CONTINUE.

Składnia:

EXIT [ etykieta ]  [ WHEN warunek wykonania ]
CONTINUE [ etykieta ]  [ WHEN warunek wykonania ]

Jeśli etykieta nie występuje, instrukcja dotyczy pętli w której jest umieszczona. Po podaniu etykiety, instrukcja odnosi się do pętli z daną etykietą, jednak musi to być pętla zewnętrzna w stosunku do bieżącej.

Warunek, wyrażenie typu logicznego, umożliwia selektywnie wykonać instrukcję; jest alternatywą dla instrukcji IF.

<<zewnetrzna>>
LOOP
	...

	<<wewnetrzna>>
	LOOP
		...
	
		-- pominięcie iteracji w pętli "wewnętrzna"	
		CONTINUE WHEN nastepny_element;

		-- przerwanie pętli "zewnętrzna
		EXIT zewnetrzna WHEN koniec_pracy;
	END LOOP;
END LOOP;

UWAGA! Podczas testów z procedurami zawierającymi pętle LOOP lub WHILE warto ustawić parametr STATEMENT_TIMEOUT na jakąś niezerową wartość, powiedzmy kilka sekund. Można oszczędzić sobie kłopotów w przypadku zapętlenia.

Pętla nieskończona - LOOP

Składnia:

[ <<etykieta>> ]
LOOP
    -- treść pętli
END LOOP [ etykieta ];

Zakończenie tej pętli jest możliwe instrukcją EXIT lub RETURN.

Przykład

Iterowanie po wynikach kursora nieograniczonego.

CREATE OR REPLACE FUNCTION lista_tabel() RETURNS SETOF text AS $$
    DECLARE
        kursor REFCURSOR;
        nazwa  text;
    BEGIN
        OPEN kursor FOR SELECT relname FROM pg_class;

        LOOP
            FETCH FROM kursor INTO nazwa;
            EXIT WHEN NOT FOUND;

            RETURN NEXT nazwa;
        END LOOP;

        CLOSE kursor;
    END;
$$ LANGUAGE 'plpgsql' STABLE;

WHILE - pętla z warunkiem stopu

Pętla WHILE wykonuje 0 lub więcej iteracji, w zależności od podanego warunku - wyrażenia logicznego.

Składnia:

[ <<etykieta>> ]
WHILE warunek LOOP
    -- treść pętli
END LOOP [ etykieta ];

Przykład

Wyszukiwanie binarne w tablicy liczb całkowitych.

CREATE OR REPLACE FUNCTION binsearch(T integer[], x integer) RETURNS integer AS $$
    DECLARE
        a   integer;
        b   integer;
        c   integer;
        y   integer;
    BEGIN
        a := array_lower(T, 1);
        b := array_upper(T, 1);

        IF a IS NULL OR b IS NULL THEN
            RETURN NULL;
        END IF;

        WHILE a <= b LOOP
            c := (a + b) / 2;
            y := T[c];

            IF x = y THEN
                -- znaleziono!
                RETURN c;
            ELSIF x < y THEN
                -- wybór przedziału [a, c - 1]
                b := c - 1;
            ELSE
                -- wybór przedziału [c + 1, b]
                a := c + 1;
            END IF;
        END LOOP;

        RETURN NULL;
    END;
$$ LANGUAGE 'plpgsql'
    STRICT
    IMMUTABLE;

-- przykład wywołania
SELECT binsearch(ARRAY[1, 2, 3, 4, 5, 7], 5);

Pętla FOR po liczbach całkowitych

Pętla ma składnię znaną z Algola lub Ady:

[ <<etykieta>> ]
FOR zmienna IN [ REVERSE ] od .. do [ BY krok ] LOOP
    -- treść pętli
END LOOP [ etykieta ];

Zmienna przechowuje liczby całkowite. Jest to całkowicie nowa zmienna, wprowadzana automatycznie, istniejąca wyłącznie w zakresie pętli.

Od i Do są wyrażeniami o wartościach całkowitych. Zakłada się, że od <= do, jeśli występuje opcjonalne słowo kluczowe REVERSE, relacja jest odwrócona.

Opcjonalne część BY krok pozwala ustalić o jaką wartość będzie przyrastała zmienna, domyślnie o 1. Krok musi być dodani.

Przykład 1

CREATE OR REPLACE FUNCTION for_test() RETURNS VOID
LANGUAGE 'plpgsql' AS $$
    BEGIN
        RAISE NOTICE 'przykład 1';
        FOR i IN 1 .. 5 LOOP
            RAISE NOTICE 'i=%', i;
        END LOOP;

        RAISE NOTICE 'przykład 2';
        FOR i IN 1 .. 5 BY 3 LOOP
            RAISE NOTICE 'i=%', i;
        END LOOP;

        RAISE NOTICE 'przykład 3';
        FOR i IN REVERSE 5 .. 1 LOOP
            RAISE NOTICE 'i=%', i;
        END LOOP;

        RAISE NOTICE 'przykład 4';
        FOR i IN REVERSE 5 .. 1 BY 3 LOOP
            RAISE NOTICE 'i=%', i;
        END LOOP;
    END;
$$;

Kolejne pętle wypiszą:

• 1, 2, 3, 4, 5
• 1, 4
• 5, 4, 3, 2, 1
• 5, 2

Przykład 2

Demonstracja przesłaniania zmiennej.

CREATE OR REPLACE FUNCTION for_test2() RETURNS integer
LANGUAGE 'plpgsql' AS $$
    DECLARE
        i   integer;
    BEGIN
        i := 123;

        FOR i IN 1 .. 15 LOOP
            -- zmienna i z zewnętrznego bloku jest przysłaniana
            NULL;
        END LOOP;

        RETURN i; -- i = 123
    END;
$$;

Wynik działania funkcji:

test=# select for_test2();
 for_test2 
-----------
       123
(1 row)

Pętla FOR po wynikach zapytań

Składnia:

<<etykieta>>
FOR lista zmiennych IN zapytanie LOOP
    ...
END LOOP [ etykieta ] ;

Zapytanie, zwykłe lub dynamiczne, powinno być postaci SELECT lista kolumn FROM .... Zwracane przez zapytanie kolumny i ich typy muszą ściśle odpowiadać liście zmiennych; wyjątkiem jest zmienna typu rekordowego, której struktura dopasuje się do typu zapytania.

Zmienne używane w iteracji muszą zostać wcześniej zadeklarowane.

Przykład - zapytanie statyczne

DECLARE
    imie    varchar(100);
    wiek    integer;
    R       record;
BEGIN
    FOR imie, wiek IN SELECT osoba_imie, osoba_wiek FROM osoby LOOP
        ...
    END LOOP;

    ...

    FOR R IN SELECT osoba_imie, osoba_PESEL, osoba_wiek FROM osoby LOOP
        ...
    END LOOP;
END;

Przykład - zapytanie dynamiczne

Funkcja zwraca listę wartości wybranej kolumny, nie mniejsze niż wartość graniczna.

CREATE OR REPLACE FUNCTION lista_liczb(tabela text, kolumna text, min integer) RETURNS SETOF integer AS $$
	DECLARE
		liczba integer;
	BEGIN
		FOR liczba IN EXECUTE
			'SELECT ' || quote_ident(kolumna) || ' FROM ' || tabela::regclass ||
			' WHERE ' || quote_ident(kolumna) || ' > $1' USING min
		LOOP
			RETURN NEXT liczba;
		END LOOP;
	END;
$$ LANGUAGE 'plpgsql' STABLE;

Pętla FOR po kursorach

W pętli można używać jedynie ograniczonych kursorów. Kursor jest automatycznie otwierany przed rozpoczęciem pętli i zamykany po jej zakończeniu.

CREATE OR REPLACE FUNCTION test(integer) RETURNS void AS $$
    DECLARE
        kursor CURSOR (wiek integer) FOR
                   SELECT * FROM osoby WHERE osoba_wiek >= wiek;

        rekord osoby%ROWTYPE;
    BEGIN
        FOR rekord IN kursor($1) LOOP
            RAISE NOTICE 'imię=%, nazwisko=%', rekord.osoba_imie, rekord.osoba_nazwisko;
        END LOOP;
    END;
$$ LANGUAGE 'plpgsql' STABLE;

Pętla FOREACH - tablice

Konstrukcja będzie dostępna od wersji 9.2

Pętla FOREACH pozwala w łatwiejszy sposób iterować po elementach tablic (ang. array) lub podtablicach w przypadku tablic wielowymiarowych.

Składnia:

FOREACH element [ SLICE n ] IN ARRAY tablica LOOP
	...
END LOOP;

Element musi być zmienną albo typu elementów tablicy, albo tablicą.

Opcjonalna konstrukcja SLICE n pozwala ustalić jaki rozmiar będą miały podtablice odczytywane przez pętlę. Wartość n nie może przekroczyć liczby rozmiarów tablicy i musi być literałem liczbowym (nie nazwą stałej).

Przykład 1

CREATE OR REPLACE FUNCTION for_each1() RETURNS VOID AS $$
    DECLARE
        tablica integer[];
        element integer;
    BEGIN
        tablica := ARRAY[10,20,30,40];
        FOREACH element IN ARRAY tablica
        -- lub
        -- FOREACH element SLICE 0 IN ...
        LOOP
            RAISE NOTICE '%', element;
        END LOOP;
    END;
$$ LANGUAGE 'plpgsql';

Wynik SELECT for_each1():

NOTICE: 10
NOTICE: 20
NOTICE: 30
NOTICE: 40

Przykład 2 - SLICE

CREATE OR REPLACE FUNCTION for_each2() RETURNS VOID AS $$
    DECLARE
        tablica integer[][][];
        --element integer;       -- dla SLICE 0
        --element integer[];     -- dla SLICE 1
        --element integer[][];     -- dla SLICE 2
        element integer[][][]; -- dla SLICE 3
    BEGIN
        -- tablica 3-wymiarowa
        tablica := ARRAY[
            ARRAY[ARRAY[1,2,3], ARRAY[4,5,6], ARRAY[7,8,9]],
            ARRAY[ARRAY[11,12,13], ARRAY[14,15,16], ARRAY[17,18,19]],
            ARRAY[ARRAY[21,22,23], ARRAY[24,25,26], ARRAY[27,28,29]]
        ];

        --FOREACH element SLICE 0 IN ARRAY tablica
        --FOREACH element SLICE 1 IN ARRAY tablica
        --FOREACH element SLICE 2 IN ARRAY tablica
        FOREACH element SLICE 3 IN ARRAY tablica
        LOOP
            RAISE NOTICE '%', element;
        END LOOP;
    END;
$$ LANGUAGE 'plpgsql';

Wynik dla SLICE 1:

NOTICE:  {1,2,3}
NOTICE:  {4,5,6}
NOTICE:  {7,8,9}
NOTICE:  {11,12,13}
NOTICE:  {14,15,16}
NOTICE:  {17,18,19}
NOTICE:  {21,22,23}
NOTICE:  {24,25,26}
NOTICE:  {27,28,29}

Wynik dla SLICE 2:

NOTICE:  {{1,2,3},{4,5,6},{7,8,9}}
NOTICE:  {{11,12,13},{14,15,16},{17,18,19}}
NOTICE:  {{21,22,23},{24,25,26},{27,28,29}}

Wynik dla SLICE 3:

NOTICE: {{{1,2,3},{4,5,6},{7,8,9}},{{11,12,13},{14,15,16},{17,18,19}},
{{21,22,23},{24,25,26},{27,28,29}}}