Procedury składowane w PostgreSQL/Język SQL

W procedurach napisanych w języku SQL mogą występować jedynie instrukcje tego języka, nie ma dostępnych żadnych konstrukcji sterujących (instrukcji warunkowych, pętli, dynamicznego tworzenia zapytań itp.).

Kolejne instrukcje oddziela średnik, rezultat ostatniej jest przekazywany jako wynik. W funkcji zwracającej jakiś wynik ostatnią instrukcją musi być SELECT, ewentualnie UPDATE, INSERT, DELETE z klauzulą RETURNING.

Jeśli funkcja zwraca jeden wiersz, a ostatnie zapytanie zwróci ich więcej, nie jest zgłaszany błąd, lecz wynikiem staje się pierwszy wiersz. Natomiast jeśli zapytanie nie zwróci żadnych wierszy, wynikiem jest NULL.

Różnice w stosunku do funkcji w PL/pgSQL

• Argumenty funkcji nie są dostępne przez nazwę, jedynie przez numer porządkowy $n.
• Treść procedury jest sprawdzana dokładniej, od razu raportowane są np. odwołania do nieistniejących tabel.
• Jeśli funkcja jest nieskomplikowana, jej treść może zostać wstawiona bezpośrednio w zapytanie, bez konieczności wywoływania procedury.
• Plany wykonywania funkcji nie są pamiętane, tylko każdorazowo przygotowywane.

Przykłady

Przykład 1

Demonstracja użycia argumentów funkcji.

CREATE OR REPLACE FUNCTION srednia_ocen(text, numeric) RETURNS numeric
    LANGUAGE SQL AS
$$
    SELECT AVG(ocena) FROM studenci
     WHERE nazwisko LIKE $1
       AND ocena >= $2;
$$;

Przykład 2

Zwracanie wyników.

CREATE OR REPLACE FUNCTION test1() RETURNS SETOF studenci
    LANGUAGE SQL AS
$$
    -- aktualizacja wierszy
    UPDATE studenci SET nazwisko = upper(nazwisko);
    -- wynik zapytania
    SELECT * FROM studenci ORDER BY nazwisko, ocena;
$$;

CREATE OR REPLACE FUNCTION test2() RETURNS studenci
    LANGUAGE SQL AS
$$
    -- wynik zapytania - tylko jeden wiersz
    SELECT * FROM studenci ORDER BY nazwisko, ocena;
$$;

CREATE OR REPLACE FUNCTION test3() RETURNS SETOF studenci
    LANGUAGE SQL AS
$$
    -- wynik zapytania
    UPDATE studenci SET nazwisko = upper(nazwisko)
        RETURNING *;
$$;

Przykład 3

Przykład włączania treści procedury do zapytania. Są tworzone dwie funkcje, jedna z PL/pgSQL, druga w SQL, obie zwracają jednakową liczbę.

CREATE OR REPLACE FUNCTION pred1() RETURNS integer
    LANGUAGE 'plpgsql'
AS $$
    BEGIN
        RETURN 123;
    END;
$$ STABLE;

CREATE OR REPLACE FUNCTION pred2() RETURNS integer
    LANGUAGE 'SQL'
AS $$
    SELECT 123;
$$ STABLE;

Zostaną użyte do prostego filtrowania:

CREATE TABLE test (x integer);
INSERT INTO test (SELECT * FROM generate_series(1, 100000));

-- 1.
EXPLAIN ANALYZE
    SELECT * FROM test WHERE x = pred1();

-- 2.
EXPLAIN ANALYZE
    SELECT * FROM test WHERE x = pred2();

Plan pierwszego zapytania:

 Seq Scan on test  (cost=0.00..25152.00 rows=472 width=4) (actual time=0.188..146.340 rows=1 loops=1)
   Filter: (x = pred1())
 Total runtime: 146.358 ms

Plan drugiego zapytania:

 Seq Scan on test  (cost=0.00..1572.00 rows=472 width=4) (actual time=0.029..15.211 rows=1 loops=1)
   Filter: (x = 123)
 Total runtime: 15.230 ms

Jak widać w drugim przypadku, w warunku Filter nie ma wywołania funkcji pred2 lecz zwracana przez nią wartość.