Procedury składowane w PostgreSQL/Język PLpgSQL/Wykonywanie zapytań

W procedurze można używać praktycznie wszystkich instrukcji SQL, za wyjątkiem związanych transakcjami, tj. BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO.

Aby wykonać zapytanie należy jego treść umieścić w kodzie procedury, wszystkie zmienne zadeklarowane w bloku oraz argumenty funkcji są wstawiane do zapytania.

Zapytania modyfikujące dane, tj. INSERT, UPDATE, DELETE, TRUNCATE oraz modyfikujące bazę, tj. CREATE, ALTER, DROP itp., nie wymagają żadnego specjalnego traktowania, powinny być wstawione wprost.

Natomiast w zależności od tego, co zamierzamy zrobić z wynikiem zapytania SELECT, używane są różne konstrukcje:

• PERFORM zapytanie - zignorowanie wyniku zapytania,
• SELECT ... INTO ... FROM ... - wczytanie kolumn z pojedynczego wiersza do zmiennych,
• FOR ... IN zapytanie LOOP - iterowanie po wierszach zapytania,
• RETURN QUERY zapytanie - zwrócenie wyniku zapytania.

Sprawdzanie stanu operacji

FOUND

W każdej procedurze dostępna jest lokalna zmienna logiczna FOUND, która informuje o statusie ostatnio wykonanej operacji.

Ustawiana jest na wartość true w następujących sytuacjach:

• gdy SELECT INTO zwróci rekord;
• gdy zapytanie wykonane w PERFORM zwróciło co najmniej jeden wiersz;
• gdy UPDATE, INSERT, DELETE spowodują zmianę co najmniej jednego rekordu;
• gdy pętla FOR (każdy wariant) wykona przynajmniej jedną iterację; FOR ustawia tę zmienną po zakończeniu iterowania, w instrukcjach wykonywanych wewnątrz pętli można więc bez przeszkód korzystać z tej zmienne;
• gdy instrukcje RETURN NEXT/RETURN QUERY zwrócą co najmniej jeden rekord;
• gdy instrukcja FETCH wczyta rekord;
• gdy instrukcja MOVE zmieni pozycję kursora.

Wszystkie te instrukcje w przeciwnym przypadku ustawiają wartość false.

Natomiast żadna inna instrukcja nie zmienia wartości FOUND. W szczególności dynamiczne zapytania tego nie robią.

Przykład

CREATE OR REPLACE FUNCTION dodaj_lub_zaktualizuj(id integer, text, text, int)
RETURNS void AS $$
	BEGIN
		-- próba aktualizacji
		UPDATE osoby SET
			imie = $2,
			nazwisko = $3,
			wiek = $4
		WHERE
			osoby.id = id
		;

		-- rekordu nie znaleziono, wstawienie nowego
		IF NOT FOUND THEN
			INSERT INTO osoby VALUES (id, $2, $3, $4);
		END IF;
	END;
$$ LANGUAGE plpgsql;

GET DIAGNOSTICS

Instrukcja GET DIAGNOSTICS pozwala odczytać status ostatnio wykonanej instrukcji, również tych wykonywanych dynamicznie. Składnia:

GET DIAGNOSTICS zmienna = nazwa

Zmienna musi zostać wcześniej zadeklarowana, nazwa jest jednym z literałów:

• ROW_COUNT - odczyt liczby zmienionych/skasowanych/wstawionych wierszy,
• RESULT_OID - wartość OID ostatnio wstawionego wiersza do tabeli z OID.

Przykład 1

CREATE TABLE test (x integer);
INSERT INTO test (SELECT i FROM generate_series(1, 1000) AS i);

CREATE FUNCTION skasuj_losowe() RETURNS integer AS $$
    DECLARE
        n	integer;
    BEGIN
        DELETE FROM test WHERE random() > 0.9;

        -- tutaj: liczba skasowanych wierszy
        GET DIAGNOSTICS n = ROW_COUNT;
        RETURN n;
    END;
$$ LANGUAGE plpgsql;

SELECT skasuj_losowe();

 skasuj_losowe 
---------------
            96
(1 row)

Przykład 2

CREATE TABLE test (x integer)
    WITH OIDS; --<<--

CREATE FUNCTION wstaw_kilka_wierszy() RETURNS oid AS $$
    DECLARE
        n	integer;
        id	oid;
    BEGIN
        n := CAST(random() * 10 AS integer);
        FOR i in 1 .. n LOOP
            INSERT INTO test VALUES (i);
        END LOOP;

        GET DIAGNOSTICS id = RESULT_OID;
        RETURN id;
    END;
$$ LANGUAGE plpgsql;

SELECT wstaw_kilka_wierszy();
SELECT wstaw_kilka_wierszy();

 wstaw_kilka_wierszy 
---------------------
               17035
(1 row)

 wstaw_kilka_wierszy 
---------------------
               17037
(1 row)

Kolumny i zmienne o tej samej nazwie

Problem nie dotyczy wersji 9.0 i nowszych - niejednoznaczności zostaną wykryte

Zmienne oraz argumenty funkcji są wstawiane do zapytań. Może to powodować różne, pozorne błędne efekty. Np.

DECLARE
	imie	text := 'Jan';
	wiek	integer := 33;
BEGIN
	... SELECT imie FROM osoby WHERE wiek = wiek ...
END;

Zapytanie jakie zostanie wykonane:

SELECT 'Jan' FROM osoby WHERE 33 = 33;

Dlatego w przypadku powtarzania się nazw kolumn i zmiennych/argumentów, należy kwalifikować kolumny nazwą tabeli (lub zmienne etykietą bloku):

SELECT osoby.imie FROM osoby WHERE osoby.wiek = wiek;

Rozwiązanie w wersji 9.0

Od wersji 9.0 takie sytuacje są wykrywane. Wartość parametru systemowego plpgsql.variable_conflict decyduje, jak postępować w takich przypadkach:

• error - zgłoszenie błędu,
• use_column - użycie kolumn,
• use_variable - użycie zmiennych.

Parametr jest globalny, ale może być również indywidualnie przypisany do procedury, np.:

CREATE FUNCTION foo() ... $$
   #variable_conflict use_column
   BEGIN
      ...
   END;
$$;

Zobacz też

• zapytania dynamiczne