PostgreSQL - funkce

Jako ve většině databází můžeme řetězit řadu vět SQL a považovat je za jednotku funkčního bloku; různé databáze popisují tuto operaci s různými názvy, uloženými procedurami, moduly, makry atd.

v PostgreSQL říkají se jim funkce. Kromě sjednocení několika příkazů SQL nám tyto funkce také umožňují spouštět příkazy SQL pomocí procedurálních jazyků (PL).

v PostgreSQL máme k tomu více možností a možnost je rozšířit.

1. Funkce PostgreSQL

Anatomie funkce
Bez ohledu na jazyk používaný k zápisu funkcí mají strukturu, tuto strukturu lze syntetizovat následujícím způsobem:

 VYTVOŘIT NEBO VYMĚNIT FUNKCI func_name (arg1_arg1datatype) NÁVRATY some_type / setoff someetype / TABLE / (…) / AS $$ BODY off function $$ LANGUAGE language_of_function

Pokud popíšeme, co vidíme, je to docela jednoduché, VYTVOŘTE NEBO VYMĚŇTE FUNKCI je klauzule o vytvoření funkce, func_name je název, který bude mít arg1, parametr, který bude přijímat, a arg1_datatype je typ dat, který je uvedeným parametrem, tj. pokud jde o celé číslo, řetězec atd. v NÁVRATY Vrátíme výsledek naší funkce, $$ je začátek bloku, který ustoupí tělu funkce a poté stejně skončí $$ a nakonec JAZYK nám umožňuje určit jazyk, ve kterém je funkce napsána.

Je to dobře definovaná a docela čitelná struktura, takže bychom neměli mít problémy s vytvářením našich funkcí.

Funkce psaní pomocí SQL
Napište funkce pomocí SQL Je to docela snadné a rychlé, je to v podstatě vzít naše příkazy SQL a přidat k nim záhlaví a zápatí funkcí a máme hotovo.

Nicméně jako všechno ostatní to stojí za cenu některých obětí, například ztrácíme flexibilitu, že pokud bychom mohli pracovat s jiným jazykem a vytvořit více větví řízení podmíněného spouštění, nemůžeme mít více než jeden příkaz SQL, i když to lze napravit pomocí různých metod.

Největší výhodou je, že protože SQL je plánovač PostgreSQL umožňuje nám využít výhod indexů a tím urychlit jejich provádění, na druhou stranu u jiných jazyků bude funkce vždy černou skříňkou

Nyní se podívejme na funkci napsanou pomocí SQL:

 VYTVOŘIT NEBO VYMĚNIT FUNKCI ins_logs (param_user_name varchar, param_description text) RETURNS integer AS $$ INSERT INTO logs (user_name, description) VALUES ($ 1, $ 2) RETURNING log_id; $$ JAZYK 'sql' VOLATILE;

Vidíme, že se řídíme strukturou definovanou výše a na konci v sekci JAZYK definujeme klauzuli 'sql' NESTÁLÝ To, že vlastní, znamená, že funkce může při každém uskutečněném volání vrátit něco jiného, ​​i když přijímá stejné parametry. Potom pro volání naší funkce můžeme použít:

 SELECT ins_logs ('lhsu', 'this is a test') As new_id;

Uděláme větu VYBRAT, funkce je aktivní a co vrátí, to obdržíme a v tomto případě uvidíme hodnotu, kterou nazýváme new_id a která funkce vrací jako log_id.
Můžeme dokonce použít funkci k provedení aktualizace záznamu a vrácení neplatného parametru jako v tomto příkladu:

 VYTVOŘIT NEBO VYMĚNIT FUNKCE upd_logs (log_id integer, param_user_name varchar, param_description text) NÁVRATY neplatné AS $$ AKTUALIZACE protokolů SET user_name = $ 2, description = $ 3, log_ts = CURRENT_TIMESTAMPWHERE log_id = $ 1; $$ LANGUAGE 'sql' VOL 'VOLATILE'

Protože jsme neplatní, nepotřebujeme pole přijímače, takže jej provedeme následovně:

 VYBRAT upd_logs (12, 'róba', 'Změnit na regina');

Zde vidíme, že jsme z předchozího hovoru odstranili poslední krok As new_id.
Tím tento tutoriál dokončíme, nyní můžeme provádět základní funkce v SQL, čímž usnadňuje a zjednodušuje mnoho činností, které můžeme potřebovat v rámci programu nebo systému, který budujeme.

2. Funkce PostgreSQL v jiných jazycích

Jedna z nejatraktivnějších vlastností PostgreSQL je, že není omezen pouze na jazyk SQLDíky načítání modulů se můžeme rozhodnout začlenit pokročilé funkce, včetně možnosti použití různých jazyků k vytváření funkcí, čímž můžeme dosáhnout velké flexibility s využitím lepších možností generování podmíněností a výhod, které jsou různým jazykům vlastní.

Funkce zápisu s PL / pgSQL
V okamžiku, kdy si všimneme, že standard SQL nedosahuje dotazů, které chceme ve funkci provádět, můžeme se vždy uchýlit k použití PL / pgSQL; jedna z jeho odlišností a vylepšení ve vztahu k SQL spočívá v tom, že lokální proměnné lze deklarovat pomocí příkazu DECLARE, můžeme mít také kontrolu nad tokem a musíme tělo funkce uzavřít do bloku BEGIN END.

Podívejme se na příklad funkce napsané v tomto jazyce:

 VYTVOŘIT FUNKCI sel_logs_rt (param_user_name varchar) RETURNS TABLE (log_id int, user_name varchar (50), text description, log_ts timestamptz) AS $$ ZAČÍT RETURN QUERY SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = param_er KONEC; $$ JAZYK 'plpgsql' STABILNÍ; 

Nyní se podívejme, jak psát funkce pomocí Pythonu.

Funkce psaní v Pythonu
Python je docela čistý programovací jazyk, který má k dispozici velké množství knihoven.
PostgreSQL je jediný databázový stroj, který vám umožňuje používat Python k vytváření funkcí.

Abychom získali možnost vytváření funkcí pomocí Pythonu, musíme se nejprve ujistit, že máme na našem serveru nainstalovaný jazyk. Jakmile víme, že ji máme nainstalovanou, musíme povolit rozšíření v PostgreSQL pomocí následujících příkazů:

 VYTVOŘIT ROZŠÍŘENÍ plpython2u; VYTVOŘIT ROZŠÍŘENÍ plpython3u; 

Před povolením rozšíření se musíme ujistit, že je Python v provozu a vyhnout se chybám.

Základní funkce v Pythonu
Jakmile budeme mít vše aktivované, abychom mohli používat Python, začneme budovat naši funkci, je důležité vědět, že PostgreSQL dokáže převést své datové typy na datové typy Pythonu a naopak. PL / Python je dokonce schopen vracet pole a složené typy.

Podívejme se níže na funkci, která provádí textové vyhledávání v online zdroji, něco, co nebylo možné provést s PL / pgSQL, na následujícím obrázku uvidíme kód a poté provedeme odpovídající vysvětlení.

• Importujeme knihovny, které budeme používat.
• Vyhledávání na webu provádíme zřetězením vstupních parametrů uživatele.
• Přečteme odpověď a uložíme ji do souboru HTML s názvem raw_html.
• Uložíme část HTML, která začíná a končí před začátkem.
• Odebereme značky HTML a prázdné znaky a proměnnou s názvem result znovu uložíme.
• Vracíme konečný výsledek.
• Další zajímavou vlastností používání Pythonu je, že můžeme komunikovat přímo s operačním systémem, podívejme se na funkci, která dělá výpis adresářů, je třeba poznamenat, že to musí vytvořit superuživatel:

 VYTVOŘIT NEBO VYMĚNIT FUNKCI list_incoming_files () NÁVRAT SETOF text AS $$ import os return os.listdir ('/ incoming') $$ LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

K čemu to je? Můžeme se zeptat sami sebe, protože si představte, že bychom chtěli nahlédnout do souborů, které máme v systému k dispozici, volání funkce by vypadalo nějak takto:

 VYBRAT název souboru FROM list_incoming_files () Jako název souboru KDE název souboru ILIKE '% .csv'

Tímto tento tutoriál dokončujeme, již zvládáme vytváření funkcí v jiných jazycích v PostgreSQL, což nám dává nekonečné pole, pokud jde o plnění našich požadavků.