Hlavní navigace

PostgreSQL: PL/pgSQL – Serverové programování 02 - anonymní blok

16. 11. 2016 9:08 | Michal Šalko

Oproti klasickým funkcím v PostgreSQL i procedurám v jiných jazycích stojí „Anonymous block“. Jedná se sérií deklarací a sekvencí procedurálních příkazů. Jeho využití je zřejmé. Používá se tam, kde potřebujeme provést určité databázové operace, aniž bychom zapisovali funkci do databáze.

Anonymní  blok

Jednou jsem mněl několik databází a potřeboval jsem srovnat počty záznamů v jednotlivých tabulkách. Postupoval jsem tak, že jsem vytvořil dočasnou tabulku (Existuje pouze v rámci jedné session).  Pomocí anonymního bloku jsem ji naplnil daty, tabulka a počet záznamů a výsledek jsem exportoval přes csv do cílové databáze, kde jsem provedl srovnání.

-- Protože jsem, než jsem to odladil prováděl několik krát, tak nejdříve jsem po sobě uklidil
drop table if exists table_sum_statistics;
create temporary table table_sum_statistics
(table_name text primary key,
count integer);

-- Vlastní anonymní block, který se spustí automaticky
DO
$$DECLARE
   r record;
   lnew_owner    text;
   lschema text;
BEGIN
    lschema = 'public';

    FOR r IN select table_name
            from information_schema.tables
            where table_type = 'BASE TABLE'
            and table_schema = lschema
    LOOP
        EXECUTE ( 'insert into table_sum_statistics select '''||r.table_name||''', count(*) from ' || r.table_name );
    END LOOP;
END$$;

--  Výslední statistika
select * from table_sum_statistics order by table_name;
-- drop table if exists table_sum_statistics; 

Moje doporučení :

V případě, že neprovádíte „kusovky“ doporučuji proměnné, které se mohou měnit. Zde je to lschema vytaženo mimo SQL příkazy aby se lehce identifikovalo případně změnilo.

Zde v anonymním bloku jsem použil konstrukci vytažení dat pomocí datového typu record.

-- Deklarace promenne typu record
 R record;
…
    -- Cyklus zápisu select výsledku do record
    FOR r IN select …

    LOOP
        -- Jak se přečte hodnota sloupce v recordu
        … r.table_name…

    END LOOP; 

Chci upozornit, za doby mé éry pod Oraclem nám Oracle kladl jedno důležité pravidlo. „Než použijete techniku přes kurzor (plánuji o tom psát, zde record) zvažte, jestli vše neobsloužíte jedním SQL příkazem. Je to rychlejší.“

Já vím, co můžete očekávat od programátora staré školy, kdy SQL bylo mnohem jednoduchší.

Jestli Vám Admin umožní ukládat funkce do db, tak můžete v duchu předchozí poučky připravit toto řešení.

CREATE OR REPLACE FUNCTION table_count(ptable_name text)
 RETURNS bigint AS
$BODY$
DECLARE
 lkol int;
BEGIN
  EXECUTE 'SELECT count(*) as count from ' || ptable_name INTO lkol;
  RETURN lkol;
END
$BODY$
LANGUAGE plpgsql;

select table_name,table_count(table_name)
            from information_schema.tables
            where table_type = 'BASE TABLE'
            and table_schema = 'public'; 

Je výrazně rychlejší.

Ale jak říkám záleží na adminovi.