Procedury składowane w PostgreSQL/Język PLpgSQL/Zapytania dynamiczne
Zapytań dynamicznych można używać w miejsce zapytań statycznych, a także przy:
- odczytywaniu danych (instrukcja EXECUTE ... INTO),
- przy iteracji (FOR ... IN EXECUTE ...),
- przy zwracaniu wyników (instrukcja RETURN QUERY EXECUTE ...),
- przy otwieraniu kursora (instrukcja OPEN ... FOR EXECUTE ...).
We wszystkich tych zastosowaniach składnia jest taka sama:
EXECUTE napis;
Od wersji 8.4 dostępna jest klauzula USING pozwalająca w bezpieczny sposób, bez narażenia się na ataki SQL injection, włączać do napisu parametry:
EXECUTE napis USING lista wyrażeń;
Argumentem EXECUTE jest napis znaków zawierający zapytanie, którego treść nie jest weryfikowane przez system w trakcie kompilacji funkcji.
Po słowie USING należy podać listę wyrażeń, które są wstawiane w napisie w miejscu numerów poprzedzonych znakiem dolara; numery odnoszą się do pozycji wyrażeń na liście.
Przy konstrukcji zapytania pomocne mogą być następujące funkcje:
- quote_ident - formatuje identyfikator (tabeli, schematu, itd.)
- quote_literal - formatuje wartość
- quote_nullable - formatuje wartość, która może być również NULL
Funkcje quote_literal i quote_nullable bezpieczniej zastąpić klauzulą USING. Natomiast lepiej zamiast quote_literal('nazwa_tabeli') lub quote_literal('nazwa_schematu.nazwa_tabeli') użyć rzutowania 'nazwa_tabeli'::regclass lub 'nazwa_schematu.nazwa_tabeli'::regclass, co pozwoli od razu wykryć, czy dana relacja i schemat istnieją, bez wykonywania zapytania. (Ponadto typ regclass można rzutować na typ OID).
Uwaga!
|
Przykład 1
[edytuj]Funkcja wykonuje zapytanie postaci SELECT kolumna FROM tabela WHERE kolumna = wartość.
CREATE OR REPLACE FUNCTION dynamiczne_zapytania(tabela text, kolumna text, wartosc integer) RETURNS integer AS $$
DECLARE
wynik integer;
BEGIN
EXECUTE 'SELECT ' || quote_ident(kolumna) ||
' FROM ' || quote_ident(tabela) ||
' WHERE ' || quote_ident(kolumna) || '=' || quote_nullable(wartosc)
INTO wynik;
RETURN wynik;
END;
$$ LANGUAGE 'plpgsql';
Przykład 2 - USING
[edytuj]CREATE OR REPLACE FUNCTION podwyzka(placa_minimalna numeric, wiek integer, podwyzka numeric) RETURNS void AS $$
BEGIN
EXECUTE
'UPDATE osoby SET osoba_placa = osoba_placa + $1'
' WHERE osoba_wiek > $2 OR osoba_placa < $3'
USING
podwyzka, -- $1
wiek, -- $2
placa_minimalna -- $3
;
END;
$$ LANGUAGE 'plpgsql';