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).

Przykład 1

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

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';