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 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 je zapsán 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.

Zavolej funkci

Funkci lze v dotazu použít předáním jakýchkoli relevantních parametrů. Napří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 vestavěné řetězcové funkce je podřetězec.

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

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

  • Řetězec (typ textu)
  • OD počátku (typ celé číslo)
  • FOR počet (typ celé číslo)

Podřetězec vrátí část vstupního textu, počínaje od znaku počáteční a konče po počet znaků. Napří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ězec.

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í.