PostgreSQL- PL/pgSQL Serverové programování 03 - Kurzory

Jednou z klíčových výhod PL/pgSQL jsou kurzory. Dovolují Vám procházet výsledek selectu a pomocí řídících příkazů jazyka modifikovat data a ty pak uložit do nějakého výsledku, nebo předat z funkce. Nechci nahrazovat manuál a podrobné příručky pro programování PostgreSQL, omezím se proto na nejčastější techniky, které využívám.

Klasický kurzor

A pojďme ihned do praxe. Máme tabulku „test1“, která obsahuje nějaká data.

drop table if exists test1;
create table test1 (id serial primary key, txt text not null);
insert into test1 (txt) values ('a'),('b'),('c'),('d'),('e'),('f'); 

Máme za úkol ke každé hodnotě ze sloupce „txt“ přidat nějaký popis. A výsledek pak má funkce vrátit. Můžeme to udělat takto :

CREATE OR REPLACE FUNCTION f_test_cursor(ppod text)
-- Výsledek je množina recodů, zde nedefinovaných
RETURNS setof record AS
$BODY$
DECLARE
  -- Zde se deklarují lokální proměnné, do ktrých se ukládá jeden řádek
  lid_test1 integer;
  ltxt_test1 text;
  ltxt_test2 text;
  -- Definice kurzoru jako selectu. Navíc je zde podmínka předávaná
  -- přes parametr ppod
  cur_test1 CURSOR FOR SELECT id,txt FROM test1 WHERE txt like ppod;
BEGIN
    -- Nejdříve se musí kurzor otevřít
    OPEN cur_test1;

    -- Pak v cyklu čteme jednotlivé řádky ze selectu
    LOOP
        -- z aktuálního řádku se načtou hodnoty do lokálních proměnných
    FETCH cur_test1 INTO lid_test1,ltxt_test1;

        -- V případě, že se už nenačetl další řádek, protože je konec výsledku
        -- ukončí se cyklus
    EXIT WHEN NOT FOUND;

        -- Příklad využití logiky nad aktuálním řádkem
        CASE ltxt_test1
          WHEN 'a' THEN
            ltxt_test2 = ltxt_test1 || ' je 1';
          WHEN 'b' THEN
            ltxt_test2 = ltxt_test1 || ' je 2';
          ELSE
             ltxt_test2 = ltxt_test1 || ' je hodně vysoké';
        END CASE;

        -- Výsledek je množina recordů a tímto returnem zabeszpečíme, že
        -- aktuální record se načte do výslední množiny
        -- Record definuji zde podle mé potřeby jako id a text
    RETURN NEXT(lid_test1,ltxt_test2);
    END LOOP;

    -- Po ukončení cyklu uzavřeme kuzor. Kurzor je aktivní během session a
    -- kdyby jsme tak neudělali, tak hrozí, že při dalším pokusu o otevření
    -- vypíše PostgreSQL chybu.
    CLOSE cur_test1;

    -- Povinně vyžadovaný return
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

-- Test výsledku
 select * from f_test_cursor('%') as (id integer, txt text);


 id |        txt
----+-------------------
  1 | a je 1
  2 | b je 2
  3 | c je hodný vysoké
  4 | d je hodný vysoké
  5 | e je hodný vysoké
  6 | f je hodný vysoké
(6 řádek) 

V příkladu jsem použil jednu zajímavou konstrukci, kde výsledek je vrácený pomocí množiny recordů. Record dle dokumentace je pouze jako placelhoder (držitel místa), který nemá přesně definovanou strukturu. Proto je nutné ji dodefinovat v sql dotazu.

Takto sestavená funkce je pouze pro účely demonstrace technik. V praxi, bychom museli si pamatovat co, že to funkce vrací v recordu. Pro tyto účely slouží definice uživatelských typů. Zde uvádím neokomentovaný příklad, který vrací stejný výsledek, ale je výhodnější pro „stabilní“ PL/pgSQL funkci.

CREATE TYPE f_test_cursor_vysledek AS (id int, txt text);

CREATE OR REPLACE FUNCTION f_test_cursor2(ppod text)
RETURNS setof f_test_cursor_vysledek AS
$BODY$
DECLARE
  lid_test1 integer;
  ltxt_test1 text;
  ltxt_test2 text;
  lf_test_cursor_vysledek f_test_cursor_vysledek;
  cur_test1 CURSOR FOR SELECT id,txt FROM test1 WHERE txt like ppod;
BEGIN
    OPEN cur_test1;
    LOOP
    FETCH cur_test1 INTO lid_test1,ltxt_test1;
    EXIT WHEN NOT FOUND;
        CASE ltxt_test1
          WHEN 'a' THEN
            ltxt_test2 = ltxt_test1 || ' je 1';
          WHEN 'b' THEN
            ltxt_test2 = ltxt_test1 || ' je 2';
          ELSE
             ltxt_test2 = ltxt_test1 || ' je hodně vysoké';
        END CASE;
        lf_test_cursor_vysledek.id = lid_test1;
        lf_test_cursor_vysledek.txt = ltxt_test2;
    RETURN NEXT lf_test_cursor_vysledek;
    END LOOP;
    CLOSE cur_test1;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

select * from f_test_cursor2('%'); 

Vše to jde udělat ještě mnoha dalšími způsoby, ale to bychom se odklonili od záměru tohoto příspěvku.

Kurzor přes record

Každého, kdo se zamyslí nad předchozím příkladem napadne, proč bychom nemohli sloučit record, loop, open, fetch, not found, close pro práci s kurzorem do jednoho příkazu ? A ten skutečně taky existuje. Jeho použití je v následujícím příklade.

-- Příklad předpokládá existenci typu f_test_cursor_vysledek
CREATE OR REPLACE FUNCTION f_test_cursor3(ppod text)
RETURNS setof f_test_cursor_vysledek AS
$BODY$
DECLARE
  -- Výsledek aktuálního načteného selectu, namísto lokálních proměnných
  lrec record;

  ltxt_test2 text;
  lf_test_cursor_vysledek f_test_cursor_vysledek;
BEGIN

    -- V cyklu se otevře kurzor nad selectem a výsledek při
    -- každém přechdu se ukládá do lokální proměnné lrec
    FOR lrec IN select id,txt from test1 WHERE txt like ppod
    LOOP

        -- record je známý již z definovaného selectu a proto se
        -- lze na něj odkazovat názvem sloupce ze selectu
        CASE lrec.txt
          WHEN 'a' THEN
            ltxt_test2 = lrec.txt || ' je 1';
          WHEN 'b' THEN
            ltxt_test2 = lrec.txt || ' je 2';
          ELSE
             ltxt_test2 = lrec.txt || ' je hodně vysoké';
        END CASE;
        lf_test_cursor_vysledek.id = lrec.id;
        lf_test_cursor_vysledek.txt = ltxt_test2;
    RETURN NEXT lf_test_cursor_vysledek;
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

-- Zde test výsledku.
select * from f_test_cursor3('%'); 

Táto technika je více pružnější než ta první a proto ji více využívám.

Tento příspěvek je pouze lehký úvod do PostgreSQL kurzorů a ve většině případů je postačující. Více v dokumentaci ke kurzorům..