Procedury składowane w PostgreSQL/Język PLpgSQL/Pętle
Etykiety[edytuj]
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[edytuj]
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[edytuj]
Składnia:
[ <<etykieta>> ]
LOOP
-- treść pętli
END LOOP [ etykieta ];
Zakończenie tej pętli jest możliwe instrukcją EXIT lub RETURN.
Przykład[edytuj]
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[edytuj]
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[edytuj]
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[edytuj]
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[edytuj]
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[edytuj]
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ń[edytuj]
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[edytuj]
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[edytuj]
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[edytuj]
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[edytuj]
 |
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[edytuj]
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[edytuj]
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}}}