Vytvoření a použití funkce ve službě Azure Database for PostgreSQL

  • 5 min

Už jsme se dozvěděli, že PostgreSQL podporuje různé jazyky. Funkce je možné kategorizovat do čtyř různých typů:

  • Funkce napsané v SQL
  • Procedurální jazykové funkce napsané v podporovaném procedurálním jazyce, jako je PL.pgSQL.
  • Interní funkce.
  • Funkce jazyka C.

Kromě toho může být účel funkce také kategorizován jako nestálé, neměnné nebo stabilní.

Nestálá funkce (výchozí) může databázi upravit a nemusí nutně vrátit stejný výsledek se stejnými vstupními parametry pokaždé. Takže pokaždé, když je tato funkce volána, musí být znovuhodnocena.

Stabilní funkce nemůže upravit databázi a vrátí stejný výsledek, pokud předá stejné argumenty a spustí se ve stejném příkazu. Pokud se tato funkce volá vícekrát, optimalizátor dotazů může použít výsledky z posledního zavolání.

Neměnná funkce nemůže upravit databázi a v případě předání stejných argumentů vrátí stejné výsledky bez ohledu na dotaz, který ji volá.

Nestálost funkce představuje velký rozdíl v efektivitě, se kterou ji zpracovává optimalizátor dotazů.

Vytvoření funkce

Funkce vrátí jednu hodnotu a lze ji použít v rámci příkazu SELECT.

Syntaxe pro vytvoření funkce je:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Stejně jako u uložených procedur se symbol $$ používá k zahájení a ukončení řetězce.

Funkce mají následující parametry:

  • name – volitelně uveďte název schématu.
  • argmode - režim argumentu. Může být IN, OUT, INOUT nebo VARIADIC. Výchozí hodnota je IN. VARDIAC je nedefinovaný počet vstupních argumentů stejného typu a následuje argumenty OUT. Argumenty OUT a INOUT nelze použít společně s notacemi FUNKCE RETURNS TABLE.
  • argname – název argumentu.
  • argtype – datový typ argumentu. Může to být základní, složené nebo doménové typy nebo odkaz na typ sloupce tabulky. Typ sloupce se zapíše jako table_name.column_name%TYPE. Tento datový typ může pomoct provést funkci nezávislou na změnách definice tabulky.
  • t_expr – výchozí hodnota (stejného typu), pokud parametr není zadaný. Výchozí hodnota má pouze parametry IN a INOUT. Vstupní parametry, které následují za parametrem s výchozí hodnotou, musí mít také výchozí hodnoty.
  • rettype – návratový datový typ, který může být základní, složený nebo doménový typ, nebo odkazovat na typ sloupce tabulky. Pokud funkce nevrací hodnotu, zadejte návratový typ jako void. Pokud jsou parametry OUT nebo INOUT, je možné vynechat klauzuli RETURNS. Pokud existuje, musí souhlasit s typem výsledku vyplývajícím z výstupních parametrů: RECORD, pokud existuje více výstupních parametrů nebo stejný typ jako jeden výstupní parametr. Modifikátor SETOF označuje, že funkce vrací sadu položek, nikoli jednu položku. Typ sloupce se odkazuje zápisem table_name.
  • column_name – název výstupního sloupce v syntaxi RETURNS TABLE. Tento parametr deklaruje pojmenovaný parametr OUT s tím rozdílem, že funkce RETURNS TABLE také znamená RETURNS SETOF.
  • column_type – datový typ výstupního sloupce v syntaxi RETURNS TABLE.
  • lang_name – jazyk použitý k zápisu procedury. Výchozí hodnota je SQL, pokud je zadána sql_body. Může to být sql, c, internal nebo název uživatelem definovaného procedurálního jazyka, například plpgsql.

Klíčová slova IMMUTABLE, STABLE nebo VOLATILE použijte jako nápovědu pro optimalizátor dotazů o funkci. Volatile je výchozí hodnota.

Volání funkce

Funkci lze v dotazu použít předáním jakýchkoli relevantních parametrů. Příklad:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Integrované funkce

PostgreSQL obsahuje mnoho předdefinovaných funkcí, které můžete použít ve svých dotazech. Tyto informace zahrnují porovnání, agregaci dat, matematické funkce atd. Úplný seznam funkcí PostgreSQL najdete v online dokumentaci.

Příkladem předdefinované řetězcové funkce je podřetězce.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Tato funkce přebírá tři vstupní parametry:

  • Řetězec (typ textu)
  • FROM start (celé číslo typu)
  • POČET FOR (celé číslo typu)

Podřetězdce vrátí část vstupního textu, počínaje počátečním znakem a zastavuje se po znaménka počtu. Příklad:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Tato funkce je stejná jako podřetěžka:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Poznámka:

Pokud znáte funkce, všimnete si, že první verze používá klíčová slova místo čárkami k oddělení argumentů. PostgreSQL poskytuje obě verze těchto funkcí.