Funkce

S funkcemi jste se již určitě setkali. Na každé kalkulačce máte funkce, napřílad sínus, mocnina, odmocnina atp. V této kapitole se budu věnovat chápání a využívání funkcí v SQL databázích.
Funkce si můžete dokonce naprogramovat vlastní, ale tomu se v této kaptiole věnovat nebudu.

Co jsou funkce

Funkce jsou vlastně databázové objekty, které však neslouží k uchovávání dat jako tabulky, ale k manipulaci s daty nebo k získávání (výpočtu) dat. S funkcemi jste se již setkali. Například statistické funkce. Funkce mohou mít argumenty a návratovou hodnotu, nebo obojí.

Argumenty jsou data, se kterými se manipuluje nebo které se používají pro získání výsledků funkce. Například funkce MAX(argument) má jeden argument (kterým je název sloupce). Argumenty se uvádějí v závorce za jménem funkce. Pokud jich je více, oddělují se čárkou.
Funkcí která nemá argumenty je například funkce RANDOM, která vrací náhodné číslo v intervalu <0,1>. I funkce bez argumentu se musí volat se závorkami za svým jménem.

Návratová hodnota funkce může být číslo, řetězec, tabulka atp. V případě agregační funkce MAX je to největší hodnota ze sloupce, který je jí předán jako argument.

Pokud chcete funkce vyzkoušet „nanečisto“, pak je můžete spustit pomocí příkazu SELECT (viz tabulka dual).

rimmer=> SELECT LENGTH('sallyx') FROM dual;
 length
--------
      6
(1 řádka)

rimmer=> SELECT RANDOM() FROM dual;
      random      
-------------------
 0.572437878232449
(1 řádka)

Druhy funkcí

Skalární funkce

Skalární funkce mají jednu hodnotu v jednom argumentu (jedno číslo, jeden řetězec, nikoliv celý sloupec) a jednu hodnotu na výstupu.
Například funkce LENGTH(retezec). Jejím argumentem je jeden řetězec a návratovou hodnotou číslo (délka řetězce předaného jako argument). Skalární funke může být součástí libovolného výrazu.

rimmer1=> SELECT
prijmeni, jmeno, LENGTH(prijmeni)+LENGTH(jmeno) AS "Délka jména"
FROM zamestnanci;
 prijmeni |  jmeno  | Délka jména
----------+---------+-------------
 Pavova   | Lenka   |          11
 Pavova   | Jana    |          10
 Mala     | Jana    |           8
 Pavova   | Lenka   |          11
 Jerry    | Tom     |           8
 Luter    | Martin  |          11
 King     | Leopold |          11
 Mann     | Tomas   |           9
 Trn      | Vasek   |           8
 Osel     | Stary   |           9
 Novy     | Pan     |           7
(11 rows)

Agregační funkce

Výstupem (návratovou hodnotou) agregační funkce je také jedna hodnota. Na vstupu je však hodnotou množina hodnot (sloupec).
Množinou hodnot jsou buď všechny řádky ve sloupci, nebo jejich část seskupená pomocí GROUP BY. Tyto funkce se mohou použít jednak v seznamu sloupců za klíčovým slovem SELECT, nebo v podmínce pro skupinu řádků za klíčovým slovem HAVING, i v klauzuli LIMIT.

Příklady použití agregačních funkcích jste viděli v předešlé kapitole.

rimmer1=> SELECT MAX(plat) FROM zamestnanci;
  max
-------
 22000
(1 row)

rimmer1=> SELECT MAX(plat) FROM zamestnanci GROUP BY oddeleni_id;
  max
-------
 12000
 15000
 22000
(3 rows)

rimmer1=> SELECT oddeleni_id, MAX(plat) FROM zamestnanci GROUP BY oddeleni_id
HAVING AVG(plat) >= 11000 ORDER BY MAX(plat) DESC;
 oddeleni_id |  max
-------------+-------
           3 | 22000
           2 | 15000
(2 rows)

Funkce SQL

Kompletní seznam funkcí najdete v oficiální dokumentaci. Já jsem pro vás vybral pár těch nejzajímavějších.

Agregační funkce

Většinu agregačních funkcí jsem ukázal v kapitole SELECT II. Pro úplnost je zde vypíšu v tabulce.

Název Význam Typ agrumentu
AVG() Vrátí průměrnou hodnotu. smallint, int, bigint, real, double precision, numeric, interval
COUNT() Vrátí počet položek v argumentu (které nejsou NULL) Všechny typy
MAX() Vrátí největší hodnotu. jakékoliv pole, číslo, řetězec, nebo datum/čas
MIN() Vrátí nejmenší hodnotu. viz MAX
SUM() Vrátí součet hodnot v množině. bigint,double precision, integer, interval, money, numeric, real, smallint
STDDEV() Spočte směrodatnou odchylku. smallint, int, bigint, real, double precision, or numeric
VARIANCE() Rozptyl hodnot v množině. smallint, int, bigint, real, double precision, or numeric

Funkce pro práci s čísly

Název Návratová hodnota
ABS(x) Absolutní hodnota.
CINT(x) Převede jakýkoliv číselný datový typ nebo řetězec (ve kterém je zapsáno číslo) na celé číslo.
CBRT(x) Třetí odmocnina x.
COS(x) Cosínus zadaného úhlu v radiánech.
EXP(x) e na x-tou, kde e=2.7182 a x je reálné číslo (nikoliv celé číslo!). Celé číslo můžete na reálné převést například pomocí funkce ROUND takto: EXP(ROUND(x,0)) (x nesmí být příliš velké číslo).
FACTORIAL(x) faktoriál x, kde x je celé číslo.
FLOOR(x) Vrátí nejbližší celé číslo menší nebo rovno argumentu.
MOD(x,y) Zbytek po celočíselném dělení x/y (x a y jsou celá čísla).
POWER(x,y) x na y
RANDOM() Náhodné číslo v intervalu <0,1> (Bez argumentu, závorky jsou přesto nutné!).
ROUND(x,y) Číslo x se zaokrouhlí na y desetinných míst.
SIN(x) Sínus zadaného úhlu v radiánech.
SQRT(x) Druhá odmocnina x, kde x je reálné číslo (nikoliv celé číslo! viz funkce EXP výše).
TAN(x) Tangens zadaného úhlu v radiánech.

Funkce pro práci s řetězci

Název Návratová hodnota
retezec
|| retezec
|| - toto není funkce ale operátor, který spojí dva řetězce v jeden. V jiných databázích (než PostgeSQL) se pro spojování řetězců používá funkce CONCAT, nebo operátor +.
Třeba MySQL používá || jako logický operátor „nebo“.
CONCAT(str,str,…) Spojení řetězců
INITCAP(x) Převede u všech slov v řetězci x první písmeno na velké a všechna další ve slově na malé.
LOWER(x) Převede všechna písmena v řetězci x na malá.
LENGTH(x) Vrací počet znaků v řetězci.
TRIM([leading | trailing | both] [characters] from string) Ořízne všechny bílé znaky (mezery, tabulátory, nová řádky) nebo characters z leva, nebo z prava, nebo zleva i prava z řetězce string.
UPPER(x) Převede všechna pímsena v řetězci x na velká.
rimmer1=> SELECT
    CONCAT('*', TRIM (' xAx '),                    '*'),
    CONCAT('*', TRIM (leading ' xAx '),            '*'),
    CONCAT('*', TRIM (trailing ' x' from ' xAx '), '*')
FROM dual;
 concat | concat | concat
--------+--------+--------
 *xAx*  | *xAx * | * xA*
(1 řádka)

Funkce pro práci s časem

Název Návratová hodnota
CURRENT_TIMESTAMP Vrací aktuální datum s časem i časovou zónou (např. 2002-12-29 20:22:31.610797+00). Toto vlastně není funkce, ale makro, proto se nepoužívají závorky! Může se použít jako DEFAULT hodnota.
CURRENT_DATE Vrací akutální datum.
CURRENT_TIME Vrací aktuální čas.
DATE(x) Převede x na datum. Argument x může být typu date, abstime, nebo text. (např. date('25.6.2002') vrátí datový typ date s hodnotou 2002-06-25).
- + * / Toto nejsou funkce, ale operátory. PostgreSQL umožňuje data odečítat, sčítat, a násobit a dělit časové intervaly. Detaily uvidíte v dokumentaci.
rimmer=> SELECT DATE('7.7.2002') - DATE('9.7.2002') FROM dual;
 ?column?
----------
       -2

rimmer=> SELECT CURRENT_TIME - '21:05' FROM dual;
      ?column?
--------------------
 18:57:19.676169+01
(1 řádka)


rimmer=> SELECT INTERVAL '1 hour' / 1.5;
 ?column?
----------
 00:40:00
(1 řádka)

Funkce pro konverzi datových typů

Název Návratová hodnota
TO_CHAR(x, vzor) Převede datum (s časem), interval nebo číslo x na řetězec podle vzoru v argumentu vzor. Jak může vypadat vzor si najdete v dokumentaci. A to jak pro data, tak pro čísla.
TO_DATE(text, vzor) Převede řetězec text na datum podle vzoru.
TO_TIMESTAMP(x, vzor) Převede řetězec nebo číslo x na timestamp s časovou zónou podle vzoru.
TO_NUMBER(text, vzor) Převede řetězec text na číslo podle vzoru.

PostgreSQL má ještě jiný mechanismus pro konverzi datových typů. A to dvě dvojtečky ::. Pokud napíšete nějakou hodnotu (nebo sloupeček), za ní dvě dvojtečky a název datového typu, PostgreSQL se pokusí hodnotu převést na tento datový typ. Tento zápis asi nebudete často potřebovat, protože PostgreSQL se pokusí převést datový typ sám od sebe všude tam, kde je to potřeba. Ale občas se s tímto zápisem můžete někde setkat (třeba v dokumentaci).

rimmer1=> SELECT
TO_CHAR(CURRENT_TIMESTAMP, 'dd.mm.YYYY HH12:MI:SS'),
TO_CHAR(interval '15h 2m 12s', 'HH24:MI:SS'),
TO_CHAR(-125.8, '999D99S'),
TO_NUMBER('12,454.8-', '99G999D9S'),
TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY'),
123.5::integer
FROM dual;
       to_char       | to_char  | to_char | to_number |      to_timestamp      | int4
---------------------+----------+---------+-----------+------------------------+------
 07.12.2013 04:29:58 | 15:02:12 | 125,80- |   -1254.8 | 2000-12-05 00:00:00+01 |  124
-- implicitně převede '3' na int
rimmer1=> SELECT '3' + 3 from dual;
 ?column?
 ----------
        6

-- pokusí se převést '3.3' na int
rimmer1=> SELECT '3.3' + 3 from dual;
ERROR:  invalid input syntax for integer: "3.3"

-- implicitně převede '3.3' na float
rimmer1=> SELECT '3.3' + 3.0 from dual;
 ?column?
----------
      6.3

-- explicitně převede '3.3' na float
rimmer1=> SELECT '3.3'::float + 3 from dual;
 ?column?
----------
      6.3

Všiměte si, jak se snaží PostgreSQL uhodnout datový typ pro převod textového řetězce podle typu čísla ve výrazu.

Ostatní funkce

PostgreSQL obshuje celou řadu dalších funkcí a operátorů. Vzpomeňte si na to, kolik obsahuje PostgreSQL zvláštních datových typů. K nim se vážou i funkce. Takže máte funkce pro práci s XML, funkce pro práci s IP adresami, geometrické funkce, funkce pro práci s JSONem atd. Všechny samozřejmě najdete v dokumentaci.

MySQL/MariaDB, SQLite, Oracle

Z předchozích kapitol už víte, že s funkcemi a operátory je to v každé DBMS jinak. Například víte, že MySQL nemá funkci TO_DATE. Nebo že operátor ^ se používá v Postgresu na umocňování, zatímco v MySQL jako logická operace XOR. V této kapitole jste se už dozvěděli, že MySQL používá operátor || jako logické OR. Pro spojování řetězců umí používat funkci CONCAT, kterou ale zase neumí SQLite.

Kromě toho, že DBMS podporují různé sady funkcí a operátorů a toho, že některé operátory občas dělají něco úplně jiného, existuje ještě jedna záludnost. Někdy funkce, i když se jmenují a dělají totéž, nedělají to stejně!

Tak třeba funkce CONCAT může mít v Oracle jen dva argumenty (spojí jen dva řetězce do jednoho). SQLite pro jistotu CONCAT vůbec nemá. MySQL i PostgreSQL umožňují zadat do CONCAT libovolný počet argumentů (které spojí všechny v jeden řetězec).

Podívejte se na následující tabulku, která ukazuje výsledky spojování řetězců, pokud je jeden ze spojovaných výrazů NULL.

Operátor/Funkce PostgreSQL MySQL SQLite Oracle
CONCAT('text',NULL) 'text' NULL 'text'
'text' || NULL NULL NULL 'text'

O tomhle chování se ani v dokumentaci nedočtete. Podle standardu a logiky věci by měl být výsledek vždy NULL. Jak je vidět, není …

Nebo funkce TRIM. Pokud budete chtít odebrat všechny mezery a hvězdičky ze začátku a konce řetězce, dopadnete asi takto:

-- PostgreSQL: vše dopadne jak má
rimmer1=> SELECT CONCAT('*',TRIM(' x' from ' xAx '),'*') FROM dual;
 concat
 --------
  *A*
  (1 řádka)
-- MySQL: z prava se nic neodebere, MySQL bere ' x' jako celý řetězec
mysql> SELECT CONCAT('*',TRIM(' x' from ' xAx '),'*') as con FROM dual;
+--------+
|  con   |
+--------+
| *Ax *  |
+--------+
-- MySQL: z leva se nic neodebere, MySQL bere 'x ' jako celý řetězec
mysql> SELECT CONCAT('*',TRIM('x ' from ' xAx '),'*') as con FROM dual;
+--------+
|  con   |
+--------+
| * xA*  |
+--------+

V SQLite pro jistotu nejde určovat, které znaky se mají odstranit.

-- Oracle neumožňuje zadat víc jak jeden znak
oracle> SELECT CONCAT(CONCAT('*',TRIM(' x' from ' xAx ')),'*') FROM dual;
SELECT CONCAT(CONCAT('*',TRIM(' x' from ' xAx ')),'*') FROM dual
                         *
ERROR at line 1:
ORA-30001: trim set should have only one character
oracle> SELECT CONCAT(CONCAT('*',TRIM(' ' from ' xAx ')),'*') FROM dual;

CONCA
-----
*xAx*
 

Co k tomu říct závěrem? Snad jen, že když používátě nějakou novou funkci, nebo „starou“ známou funkci (nebo operátor) ale v jiném DBMS, přečtěte si k ní pozorně dokumentaci!

A ještě jedna blbůstka: MySQL vyžaduje, aby mezi jménem funkce a úvodní závorkou před argumenty nebyla mezera. Jinak dostanete na první pohled nepravdivé chybové hlášení:

mysql> select max (plat) from zamestnanci;
ERROR 1630 (42000): FUNCTION rimmer1.max does not exist.
Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Odkazy na dokumentace funkcí a operátorů

Komentář Hlášení chyby
Vytvořeno: 11.9.2005
Naposledy upraveno: 9.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..