SQL příkaz

Použití SQL příkazů v SQL/PSM

Do kódu lze vkládat prakticky libovolně SQL příkazy, které lze parametrizovat použitím proměnných v zápisu SQL příkazu. Je však třeba respektovat následující omezení:

• nelze vložit SQL příkaz CREATE FUNCTION,
• proměnná se nesmí objevit na pozici, kde by měla význam identifikátoru sloupce nebo identifikátoru tabulky.

V případě, že výše zmíněné podmínky nedokážeme splnit, musíme použít tzv. dynamické SQL. V tom případě však musíme být opatrní a dbát na bezpečnost, aby se dynamicky prováděné příkazy nedaly zneužít k napadnutí aplikace metodou SQL injection.

Následující fragment inicializuje obsah tabulky series vzestupnou řadou od 1 do n:

  SET _i = 1;
  WHILE _i <= 100 DO
    INSERT INTO series VALUES(_i);
    SET _i = _i + 1;
  END WHILE

S výjimkou použití SQL příkazu jako tabulkového výrazu (viz. SRF Funkce) nelze použít příkaz SELECT jinak než s klauzulí INTO.

Použití kurzorů

V PL/pgPSM lze deklarovat a používat volné kurzory. To jsou ty kurzory, které jsou deklarovány mimo PL/pgPSM funkci a do těla se přenášejí prostřednictvím typu REFCURSOR nebo určující SQL příkaz je určen až v příkazu OPEN. Životní cyklus volných kurzorů není omezen na blok a kurzor je do svého uzavření přístupný prostřednictvím jména i vně funkce. Vázané kurzory (určující SQL příkaz je uveden v příkazu DECLARE) jsou omezeny na blok, ve kterém jsou deklarovány a po dokončení bloku jsou všechy otevřené vázané kurzory automaticky uzavřeny (tj. není nutné volat příkaz CLOSE).

Kurzor si můžete představit jako analogii deskriptoru souboru. Před použitím jej musíme aktivovat (otevřít) a po použití deaktivovat (zavřít). K tomu slouží SQL příkazy OPEN a CLOSE. Data z tabulky zpřístupněné kurzorem získáme příkazem FETCH.

PL/pgPSM podporuje tzv. scrollable kurzory, kdy můžeme kurzor ve výsledné množině volně přesouvat na libovolnou pozici, která je zadaná relativně nebo absolutně. Oproti tomu tzv. forward kurzor lze pousouvat pouze dopředu o jeden řádek. Pokud nepoužijeme atribut SCROLL v deklaraci kurzoru, pak deklarovaný kurzor bude typu forward kurzor.

Následující příklad obsahuje ukázku scrollable kurzoru (tabulka se čte od zadu).

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
  BEGIN
    DECLARE a, b integer;
    DECLARE sqlstate char(5);
    DECLARE cx SCROLL CURSOR FOR SELECT * FROM Foo;
    OPEN cx;
    -- cti z posledniho zaznamu
    FETCH ABSOLUTE -1 FROM cx INTO a, b;
    WHILE sqlstate = '00000' DO
      PRINT a, b;
      FETCH PRIOR FROM cx INTO a, b;
    END WHILE;
    -- implicitni zavreni kurzoru 
  END;
$$ LANGUAGE plpgpsm;

Pozn: pokud to není nezbytné, používejte pouze forward kurzory. V tomto příkladu se lze vyhnout scrollable kurzoru použitím klauzle ORDER BY DESC. V případě. že to není nutné, nepoužívejte kurzory vůbec. Hromadný UPDATE tabulky pomocí jednoho SQL příkazu je několikanásobně rychlejší než UPDATE jednoho řádku opakovaný pro každou řádku tabulky. Patrně existují případy, kdy je použití kurzoru nezbytné nebo efektivnější, ve většině případů jej lze nahradit hromadným SQL příkazem a použitím SQL konstrukce CASE.

Zdrojem kurzoru může být také předzpracovaný příkaz. Potřebné parametry se předají klauzulí USING v příkazu OPEN.

Funkce vracející tabulky

Výsledná tabulka může být výsledkem libovolného SQL příkazu, jehož výsledkem je tabulka (např. INSERT INTO RETURNING, nikoliv ale pouhé INSERT INTO), který se použije jako tělo PL/pgPSM funkce. V tomto případě jde vždy o funkci obsahující pouze tento příkaz. V případě. že použijeme složený příkaz, může být SQL příkaz parametrem příkazu RETURN (použití tzv. tabulkového výrazu). Další možností je generovat tabulku postupně po řádcích příkazem RETURN NEXT. Pro generování výstupní tabulky nelze kombinovat RETURN NEXT a RETURN s tabulkovým výrazem.

CREATE OR REPLACE FUNCTION filter(p int)
RETURNS SETOF Foo AS
$$
  SELECT * 
     FROM Foo
    WHERE a = p;
$$ LANGUAGE plpgsql;

Jelikož se výsledek SQL příkazu předává rovnou jako výsledek funkce, nepoužívá se klauzule INTO. Výsledkem se již neplní žádné proměnné. Je to jediný případ, kdy lze v PL/pgPSM použít SELECT bez klauzule INTO.

Tyto funkce nevoláme ve skalárním kontextu, ale v tabulkovém. Tj. na funkci pohlížíme jako na parametrizovanou tabulku. Funkci voláme se všemi IN a INOUT argumenty. V případě potřeby lze přejmenovat výstupní sloupce. Relativně často (jestliže tabulku generujeme pouze na základě globálních proměnných) se volání funkce zapouzdřuje do pohledu. Tím je voláni funkce naprosto transparentní.

-- skalární kontext
SELECT sin(0.33);

-- tabulkový kontext
SELECT * FROM filter(2);

-- tabulkový kontext a přejmenování parametrů
SELECT * FROM filter(2) f(c1, c2);

-- zafixování argumentů a vytvoření pohledu
CREATE VIEW Filter_2 AS 
       SELECT * FROM filter(2) f(c1, c2);

Holdable kurzory

Kurzory v PostgreSQL mohou být označené jako holdable. Tento příznak způsobí, že kurzor není zrušen po dokončení transakce, ale existuje, dokud jej explicitně nezrušíme. Holdable kurzory bohužel nelze použít přímo v PL/pgPSM (nejsou podporovány rozhraním SPI, které PL/pgPSM používá pro přístup k funkcím PostgreSQL. PL/pgPSM ale podporuje typ REFCURSOR. Položky tohoto typu nesou název ať již existujícího nebo neexistujícího kurzoru. Pomocí proměnné typu refcurzor lze dopravit odkaz do procedury a pomocí vrácené hodnoty z funkce tohoto typu lze kurzor exportovat z procedury. To je oproti standardu krok navíc. Příznak WITH HOLD v PL/pgPSM nevytvoří skutečný holdable kurzor (protože jej v tuto chvíly vytvořit nelze) ale zajistí, že se kurzor automaticky neuzavře při opuštění bloku a tudíž je možné jej exportovat.

CREATE OR REPLACE FUNCTION fx(a integer) 
RETURNS refcursor AS
$$
  BEGIN
    DECLARE cx CURSOR WITH HOLD FOR
            SELECT * FROM Foo WHERE Foo.a = a;
    OPEN cx;
    RETURN cx;
  END;
$$ LANGUAGE plpgpsm;

root=# select fx(3);
 f  
----
 cx
(1 row)

root=# fetch cx;
 a | b 
---+---
 3 | 7
(1 row)

Příznak WITH HOLD lze použít také pro dynamické kurzory. Pokud není určeno jinak, kurzor není holdable.