PostgreSQL: PL/pgSQL – Serverové programování 01

Někdy je potřeba provést náročné výpočty, přesuny dat, nebo vytvořit business rules (obchodní pravidla).  Tehdy sáhnete pravděpodobně po serverovém programování buďto přímo přes rozhraní, nebo po procedurálních jazycích. PostgreSQL nabízí plnou náruč procedurálních jazyků PL/pqSQL, PL/Tcl,PL/Perl,PL/Python atd.

Já pocházím ze starší  generace, která vyrůstala na jazyku 4GL (jazyk 4. generace) a kdy v 90. letech minulého století vládl Oracle a PL/SQL. Takže relační datový model, serverové programování, silná databáze a PL/SQL mám v krvi. V tomto mém příspěvku Vám chci sdělit pár základních pravidel, která se snažím dodržovat v PL/pgSQL a seznámit Vás s tímto procedurálním jazykem.

Základní struktura

Základní struktura PL/pgSQL umožňuje uložit práci ve formě funkce, nebo použít anonymní blok. Anonymní blok je série příkazů v jednom bloku, které potřebujete spustit,  aniž byste uložili svoji práci do databáze formou funkce.

1. Funkce

Než bych o tom dlouho vykládal, spusťte pgAdmina. Otevřete databázi, a v toolosech query (ctrl + e).

A zadejte tento sql script.

create table stanice
(stanice_id serial primary key,
 stanice text not null unique);

create table stanice_prumer_teplota_mesic
(
 stanice_id integer not null,
 rok    smallint not null check (rok > 2000 and rok < 2100),
 mesic  smallint not null check (mesic >=1 and mesic <= 12),
 teplota numeric(3,1) not null,
 primary key (stanice_id,rok,mesic)
);

alter table stanice_prumer_teplota_mesic
add constraint fk_stanice_prumer_teplota_mesic_stanice foreign key (stanice_id)
references stanice (stanice_id);

create table log_error
(id serial primary key,
 datum timestamp not null,
 zdroj text not null,
 parametry text not null,
 message text not null,
 table_name text,
 schema_name text);

CREATE OR REPLACE FUNCTION f_stanice_mesic_data_insert
   (pstanice text,    -- název stanice
    prok     integer, -- rok měření
    pmesic   integer, -- měsíc měření
    pteplota numeric(3,1)) -- teplota
RETURNS smallint AS -- 1 vše OK - 0 chyba
$BODY$
DECLARE
  lstanice_id integer;

  lmessage    text;
  lexp_detail text;
  lexp_hint   text;
  lexp_table  text;
  lexp_schema text;
BEGIN
  /*
  * Vlozeni dat o mesicnich prumernych teplotach
  *
  * tables : stanice, stanice_prumer_teplota_mesic
  *
  * verze : 1
  *
  */
  BEGIN
  -- vlozeni stanice
  select stanice_id into lstanice_id
     from stanice
     where stanice = pstanice;
  IF (lstanice_id is null) THEN
      INSERT INTO stanice (stanice)
      values (pstanice);
      lstanice_id = lastval();
  END IF;

    insert into  stanice_prumer_teplota_mesic
        ( stanice_id,rok,mesic,teplota)
        values
        (lstanice_id,prok,pmesic,pteplota);
    /*

    Osetrene chybove stavy :
        23000   integrity_constraint_violation
        23001   restrict_violation
        23502   not_null_violation
        23503   foreign_key_violation
        23505   unique_violation
        23514   check_violation
        23P01   exclusion_violation

    */
 EXCEPTION
    WHEN SQLSTATE '23000' OR SQLSTATE '23001' OR SQLSTATE '23502' OR
             SQLSTATE '23503' OR SQLSTATE '23505' OR SQLSTATE '23503' OR
             SQLSTATE '23514' OR SQLSTATE '23P01' THEN

             GET STACKED DIAGNOSTICS lmessage = MESSAGE_TEXT,
                                     lexp_detail = PG_EXCEPTION_DETAIL,
                                     lexp_hint = PG_EXCEPTION_HINT,
                                     lexp_table = TABLE_NAME,
                                     lexp_schema = SCHEMA_NAME;
              insert into log_error
              (datum, zdroj, parametry, message, table_name,schema_name)
              values
              (now(),'FUNCTION f_stanice_mesic_data_insert',
               'pstanice:'||coalesce(pstanice,'') ||
           ', prok:' ||coalesce(prok::text,'') ||
           ', pmesic:'||coalesce(pmesic::text,'') ||
           ', pteplota:'||coalesce(pteplota::text,''),
           lmessage ||' -- '|| lexp_detail,
        lexp_table,lexp_schema);

        RETURN 0;
 END;
 RETURN 1;

END;
$BODY$
  LANGUAGE plpgsql; 

SQL script vytvoří tři tabulky „stanice“ a „stanice_prumer_teplota_mesic“ a „log_error“. Znamená to, že budeme evidovat meteostanice a k nim průměrné měsíční hodnoty v jednotlivých měsících daného roku. A chyby v zadávaní dat budou v tabulce „log_error“. U tabulek si všimněte, že jsem věnoval čas tomu, abych zabezpečil integritu jednak formou cizího klíče, not null, datovým typem a checkem. Pak jsem sestavil funkci, která na základě vstupních dat vloží data do příslušné tabulky. Funkci spustíte takto:

select * from f_stanice_mesic_data_insert('Brno, Tuřany'::text,2016,1,-1.0); 

Funkce vám vrátí 1 – insert proběhl, nebo 0 – vyskytla se chyba. V případě chyby je log zapsán v tabulce „log_error“. V případě, opětovného spuštění

select * from f_stanice_mesic_data_insert('Brno, Tuřany'::text,2016,1,-1.0); 

do log_error se vypíše toto chybové hlášení:

select * from log_error;
  1 | 2016-11-09 18:32:35.236833 | FUNCTION f_stanice_mesic_data_insert |
pstanice:Brno, Tuřany, prok:2016, pmesic:1, pteplota:-1.0 |
 duplicate key value violates unique constraint "stanice_prumer_teplota_mesic_pkey" -- Key (stanice_id, rok, mesic)=(1, 2016, 1) already exists. |
 stanice_prumer_teplota_mesic | public 

A nyní co jsem v definici funkci udělal a proč.

• Funkce vždy vrací nějaká data. Lze použít i void. Zkušenost mi říká, že vždy použijte návratovou hodnotu, která indikuje provedení funkce. Zde jsem použil nezvyklou konstrukci BEGIN EXCEPTION, kde jsem odchytil chybu, která mohla vzniknout v rámci vkládaní dat. Když použijete tuto konstrukci, uložte si info o chybě do nějaké logovací tabulky! Jinak nebudete vvědět, proč a na čem to spadlo.
• Vlastní kód je definován v rámci bloku ohraničený begin a end (Pascal). Lokální proměnné jsou deklarovány v sekci declare.
• Proměnné jsou trojího typu parametry, lokální a databázové. Databázové jsou přístupné pouze formou SQL příkazů. Parametry vždy dávám s prefixem p a lokální l. To z toho důvodu, že někdy může dojít ke konfliktům v názvu pro SQL dotazy a pak se nestačíte divit, výsledkům.
• Pravidlem je v případě, že mám čas je uložit info o funkci za begin do poznámek. Pouze v tomto případě, máte zaručeno, že info se uloží spolu s definicí funkce. Nezapomeňte na stručné info o tom co funkce dělá a v jaké verzi a datum kdy došlo k modifikaci. Případně popsat vstupy a výstupy. Věřte mi, že pak Vám to pomůže v desítkách funkcí se orientovat. A dohledat jestli je instalována ta správná funkce.

SQL skripty si pravidelně ukládám do textového souboru. Pro Query ikona s disketou.

V případě, že potřebujete ladit funkci lze to. Viz https://www.pgadmin.org/doc­s4/dev/debugger.html . Pouze Vám doporučuji dělat debuggování nejlépe na lokální databázi, abyste zbytečně neblokovali zdroje databáze. Vykonávaní PL/pgSQL probíhá v jedné transakci co má výhody i nevýhody. Hlavně alokaci zdrojů db. Ale lze se obejít i bez debuggeru. Ne vždy jsou podmínky na to, aby mohl být zapnutý. V tomto případě používám techniky temp tabulky, checkpointy, redukce vzorových dat a dělení kódu. Ale o tom někdy jindy.

pgAdmin velice dobře poslouží i jako kontrola nainstalovaných funkcí. Může se k tomu využít kliknutí na hlavičku funkce pravým tlačítkem a vybrat „Skripty“ a „Skript CREATE“. Nebo script vidíte na záložce „panel SQL“. Pokračování příště …

Poznámka :

Text neprošel jazykovou úpravou. Jedná se u můj blog, kde podstatné jsou technikálie. Nechám otevřenou diskusi, protože věřím, že bude mnoho námětů a připomínek. Jakýkoliv příspěvek, který se nezabývá technikáliemi vymažu. Děkuji za pochopení.