Zobrazení zdroje stránky Pole v PostgreSQL

[[Category:Články]]
Pole a relační databáze nejdou dost dobře dohromady - alespoň na první pohled (viz první normální forma a požadavek nedělitelnosti ukládaných hodnot). Rozhodně podpora polí v relačních databázích není běžná, a myslím si, že je to možná i dobře. V databázovém systému, který pole podporuje, lze k databázi přistupovat skutečně jako k pouhému úložišti dat. V systémech, které podporují vícerozměrná pole, může být serializace (a zrovna tak deserializace) objektů, když ne triviální, tak jednoduchá. Ovšem riskujeme výkonnostní problémy - SQL databáze jsou optimalizované na jiný (normalizovaný) datový model. A to nemluvím o obtížích, které bychom s tímto datovým modelem měli, pokud bychom chtěli navrhovat ad-hoc SQL dotazy. Je to paradox - ORM systémy pole prakticky nepoužívají. 

Výjimkou, která potvrzuje pravidlo o ukládání polí v databázích, jsou časové řady. Minimálně v PostgreSQL jsou pole jediným efektivním prostředkem pro ukládání časových řad (jediným, který je dostupný běžnému uživateli). S výjimkou časových řad to opravdu skoro vypadá tak, že pole a relační databáze nejdou k sobě. Opak je pravdou. Podpora polí je zásadní pro SQL uložené procedury. Obecně - v kterémkoliv procedurálním jazyce se bez polí neobejdeme - a jazyky uložených SQL procedur nejsou výjimkou. Pokud podpora polí chybí (např. T-SQL), tak je to na úkor funkčnosti prostředí - chybějící funkčnost se musí [http://www.sommarskog.se/arrays-in-sql-2005.html všelijak obcházet] - což se zákonitě musí projevit na efektivitě vývojáře, čitelnosti kódu i výkonu aplikace. 

Implementace polí v PostgreSQL je poměrně unikátní a to jak v porovnání s ostatními OSS databázemi, tak v porovnání s proprietárními databázovými systémy. S použitím několika málo funkcí můžeme řešit úlohy, které bychom v jiných prostředích řešili pracněji nebo méně efektivně. 

==Datový typ pole==
PostgreSQL podporuje vícerozměrná (tedy i jednorozměrná) pole hodnot skalárních nebo složených typů. V PostgreSQL jsou [http://www.postgresql.org/docs/8.4/interactive/arrays.html pole] dynamická. Proměnná (sloupec) typu pole se deklaruje pomocí dvojice hranatých závorek "[]" zapsaných za libovolný skalární typ. Specifikovat lze i velikost pole, nicméně tato hodnota se později ignoruje:
<pre>CREATE TABLE test(a varchar[]);

-- starý zapis
INSERT INTO test VALUES('{a,b,c}');

-- novější zápis s konstruktorem pole
INSERT INTO test VALUES(ARRAY['a','b','c']); 

--Pozor - horní index pole se lze zapsat, ale nikam se neuloží a stejně tak se nepoužívá
postgres=# CREATE TABLE test(a varchar[2]);
CREATE TABLE

postgres=# INSERT INTO test VALUES(ARRAY['a','b','c']);
INSERT 0 1

postgres=# \d test
           Table "public.test"
 Column |        Type         | Modifiers 
--------+---------------------+-----------
 a      | character varying[] | 
</pre>
Pole se indexují od jedné (pokud neurčíme jinak). Prostřednictvím indexů můžeme přistupovat k jednotlivým prvkům pole, případně, pomocí intervalu, k podpoli:
<pre>postgres=# SELECT * FROM test;
    a    
---------
 {a,b,c}
(1 row)

postgres=# SELECT a[1], a[2:3] FROM test;
 a |   a   
---+-------
 a | {b,c}
(1 row)</pre>

==Základní funkce pro operace s datovým typem pole==
PostgreSQL je, jako ostatně každá SQL databáze, silná v operacích nad množinami (tabulkami). Proto je častým trikem převedení pole na tabulku, provedení určité operace, a převod výsledné množiny zpět na pole. K transformaci pole na tabulku slouží funkce <i>unnest</i>. Agregační funkcí <i>array_agg</i> získáme pole z (pod)množiny hodnot. 

Typickým vstupem je seznam hodnot oddělených vybraným znakem (separátorem - oddělovačem). K operacím nad seznamy hodnot uložených v řetězci můžeme použít funkce <i>string_to_array</i> (transformuje řetězec na pole) a <i>array_to_string</i> (generuje řetězec z pole). Pokud na pole převádíme kompletní výsledek dotazu, můžeme použít konstruktor pole z poddotazu - <i>ARRAY</i>(subselect).
<pre>postgres=# SELECT unnest(a) FROM test;
 unnest 
--------
 a
 b
 c

CREATE TABLE jmena(a varchar);
INSERT INTO jmena VALUES('Pavel'),('Petr'),('Jan'),('Zbyšek'),('Bohuslav');

postgres=# SELECT * FROM jmena;
    a     
----------
 Pavel
 Petr
 Jan
 Zbyšek
 Bohuslav
(5 rows)

postgres=# SELECT array_agg(a) FROM jmena;
            array_agg             
----------------------------------
 {Pavel,Petr,Jan,Zbyšek,Bohuslav}
(1 row)

postgres=# SELECT array_to_string(array_agg(a),'|') FROM jmena;
        array_to_string         
--------------------------------
 Pavel|Petr|Jan|Zbyšek|Bohuslav
(1 row)

-- seřazený generovaný seznam
postgres=# SELECT array_to_string(ARRAY(SELECT a FROM jmena ORDER BY a),'|');
        array_to_string         
--------------------------------
 Bohuslav|Jan|Pavel|Petr|Zbyšek
(1 row)

CREATE TABLE prefixes(country varchar, p varchar);
INSERT INTO prefixes VALUES('cs','724,777,728');

postgres=# SELECT * FROM prefixes ;
 country |      p      
---------+-------------
 cs      | 724,777,728
(1 row)

-- normalizace
postgres=# SELECT country, unnest(string_to_array(p,',')) FROM prefixes ;
 country | unnest 
---------+--------
 cs      | 724
 cs      | 777
 cs      | 728
(3 rows)

-- přepis funkce unnest do SQL
CREATE OR REPLACE FUNCTION myunnest(anyarray)
RETURNS SETOF anyelement AS $$
SELECT $1[i] FROM generate_subscripts($1,1) g(i)
$$ LANGUAGE sql;

postgres=# SELECT myunnest(ARRAY[3,4,5]);
 myunnest 
----------
        3
        4
        5
(3 rows)</pre>
Výše uvedené funkce jsou dostačující. Představme si, že dostaneme soubor ve formátu xls, který obsahuje registrovaná předčíslí národních telefonních operátorů ve tvaru kód země a seznamu předčíslí (prefixů) oddělených čárkou. Takový soubor skutečně existuje. Je docela dobře možné, že  xls-ko je nejpoužívanějším formátem pro přenos databázových dat - bohužel nebo bohudík. Díky xls nemáme problémy s kódováním - kdo pamatuje FoxPro, ví co mám na mysli. Na druhou stranu - data z dokumentů ve formátu xls lze jen výjimečně použít bez předchozího čištění.

Vlastní převod dat do PostgreSQL je otázkou několika minut. V prvním kroku vyčistíme tabulku od komentářů, nadpisů a případného dalšího balastu a soubor převedeme do formátu [http://cs.wikipedia.org/wiki/CSV csv]. Příkaz [http://www.postgresql.org/docs/8.4/interactive/sql-copy.html <i>COPY</i>] formát csv podporuje (Pozor - v případě importu csv vytvořeném v Microsoft Excelu s nastaveným českým prostředím je nezbytné použít klauzuli <i>DELIMITER</i> (výchozí oddělovač formátu csv je čárka, která je (v české mutaci Excelu) nahrazena středníkem)). 


Pokud bychom měli ze svých dat generovat data v podobném tvaru, pak stačí použít funkce: array_agg a array_to_string. Jako bonus můžeme prefixy seřadit:
<pre>postgres=# SELECT country, array_to_string(ARRAY(SELECT unnest(string_to_array(p,',')) 
                                                        ORDER BY 1),',') 
                   FROM prefixes ;
 country | array_to_string 
---------+-----------------
 cs      | 724,728,777
(1 row)</pre>
případně v kombinaci s příkazem COPY:
<pre>postgres=# COPY (SELECT country, array_to_string(ARRAY(SELECT unnest(string_to_array(p,',')) 
                                                         ORDER BY 1
                                                      ),
                                                 ',') 
                    FROM prefixes
                ) 
              TO stdout CSV;
cs,"724,728,777"</pre>
Postup: vstupní seznam hodnot oddělených čárkou byl převeden na pole, dále na tabulku, seřazen  prostřednictvím standardní klauzule ORDER BY. Seřazená tabulka se převedla opět na pole a pole se převedlo zpět na text. V Microsoft Excelu nebo v Open Office Calcu můžeme exportované csv-čko převést do formátu xls.

Fantazii se meze nekladou. Složitější SQL dotazy si můžeme zjednodušit zapouzdřením bloků SQL do tzv vlastních SQL funkcí. Příkladem může být funkce <i>unpack_domains</i>. Tato funkce generuje hierarchii doménových jmen. Např. pro kix.fsv.cvut.cz - (kix.fsv.cvut.cz, fsv.cvut.cz, cvut.cz, cz). 
<pre>CREATE OR REPLACE FUNCTION unpack_domains(text) 
RETURNS SETOF text AS $$ 
SELECT array_to_string(a.f[ i : array_upper(a.f,1) ],'.') 
   FROM generate_subscripts(string_to_array($1,'.'),1,true) g(i), 
        (SELECT string_to_array($1,'.')) a(f)
$$ LANGUAGE sql;

postgres=# SELECT unpack_domains('kix.fsv.cvut.cz');
 unpack_domains  
-----------------
 cz
 cvut.cz
 fsv.cvut.cz
 kix.fsv.cvut.cz
(4 rows)</pre>
K čemu je to dobré? Představme si, že máme zpracovat log přístupů obsahující doménové adresy klientů, přičemž chceme vědět z jakých domén a v jakém počtu se na naše zařízení přistupovalo.
<pre>CREATE TABLE log(a varchar);
INSERT INTO log VALUES('kix.fsv.cvut.cz'),('lmc.eu'),('inway.cz'),('gmail.com'),('josef.fsv.cvut.cz');

postgres=# SELECT * FROM log;
        a        
------------------
 kix.fsv.cvut.cz
 lmc.eu
 inway.cz
 gmail.com
 josef.fsv.cvut.cz
(4 rows)

postgres=# SELECT count(*), unpack_domains(a) as domain
              FROM log 
             GROUP BY unpack_domains(a) 
             ORDER BY 1 desc;
 count |      domain       
-------+-------------------
     3 | cz
     2 | cvut.cz
     2 | fsv.cvut.cz
     1 | eu
     1 | josef.fsv.cvut.cz
     1 | gmail.com
     1 | inway.cz
     1 | com
     1 | lmc.eu
     1 | kix.fsv.cvut.cz
(10 rows)</pre>
S takovým reportem bychom nejspíš neuspěli. Chtělo by to lépe jej uspořádat a to alespoň podle obráceného názvu domény:

Zde nastane první problém. V PostgreSQL nemáme funkci pro zrcadlové prohození znaků v řetězci. Můžeme si ji však napsat v [http://www.postgresql.org/docs/8.4/interactive/plpgsql.html <i>PL/pgSQL</i>], [http://www.postgresql.org/docs/8.4/interactive/plperl.html <i>Perlu</i>], [http://www.postgresql.org/docs/8.4/interactive/plpython.html <i>Pythonu</i>], v [http://www.postgres.cz/index.php/N%C3%A1vrh_a_realizace_UDF_v_c_pro_PostgreSQL jazyce <i>C</i>], a nebo v jazyce SQL:
<pre>CREATE OR REPLACE FUNCTION rvrs(text) 
RETURNS text AS $$ 
SELECT array_to_string(array_agg(a.f[i]),'') 
   FROM generate_subscripts(regexp_split_to_array($1,''),1, true) g(i), 
        (SELECT regexp_split_to_array($1,'')) a(f) 
$$ LANGUAGE sql;

postgres=# select rvrs('ahoj');
 rvrs 
------
 joha
(1 row)</pre>

Funkce <i>generate_subscripts</i> generuje indexy pro zadené pole. Pokud je třetí (volitelný) parametr roven hodnotě true, pak jsou indexy generovány v sestupném pořadí. Funkce <i>regexp_split_to_array</i> generuje pole na základě shody s regulárním výrazem. Pokud není regulární výraz zadán, pak prvek pole odpovídá znaku v řetězci.
<pre>postgres=# SELECT count(*), unpack_domains(a) as domain 
              FROM log 
             GROUP BY unpack_domains(a) 
             ORDER BY rvrs(unpack_domains(a));
 count |      domain       
-------+-------------------
     1 | com
     1 | gmail.com
     1 | eu
     1 | lmc.eu
     4 | cz
     3 | cvut.cz
     3 | fsv.cvut.cz
     1 | josef.fsv.cvut.cz
     1 | kix.fsv.cvut.cz
     1 | inway.cz
(10 rows)</pre>

Už je to skoro ono - jen je tu určité riziko - řadí se od konce názvů, nikoliv od začátku. Chtělo by to spíše reverz celého pole:

<pre>/* ukázka polymorfní funkce - pro libovolné pole */
CREATE OR REPLACE FUNCTION rvrs(anyarray) 
RETURNS anyarray AS $$ 
SELECT ARRAY(SELECT $1[i] 
   FROM generate_subscripts($1, 1, true) g(i)) 
$$ LANGUAGE sql;

postgres=# SELECT rvrs(string_to_array('kix.fsv.cvut.cz','.'));
       rvrs        
-------------------
 {cz,cvut,fsv,kix}
(1 row)

postgres=# SELECT rvrs('kix.fsv.cvut.cz');
      rvrs       
-----------------
 zc.tuvc.vsf.xik
(1 row)

/* + ukázka přetížení funkce rvrs */
postgres=# SELECT count(*), unpack_domains(a) as domain 
   FROM log 
  GROUP BY unpack_domains(a) 
  ORDER BY (rvrs(string_to_array(unpack_domains(a),'.')))[1],
           (rvrs(string_to_array(unpack_domains(a),'.')))[2] nulls first,
           (rvrs(string_to_array(unpack_domains(a),'.')))[3] nulls first,
           (rvrs(string_to_array(unpack_domains(a),'.')))[4] nulls first;
 count |      domain       
-------+-------------------
     1 | com
     1 | gmail.com
     4 | cz
     3 | cvut.cz
     3 | fsv.cvut.cz
     1 | josef.fsv.cvut.cz
     1 | kix.fsv.cvut.cz
     1 | inway.cz
     1 | eu
     1 | lmc.eu
(10 rows)</pre>

S tímto pořadím již můžeme být spokojeni.

==Pole a dynamické SQL==
Při sestavování dynamického SQL příkazu je vhodné na SQL identifikátory aplikovat funkci <i>quote_ident</i>. Tím zabezpečujeme své aplikace proti [http://en.wikipedia.org/wiki/SQL_injection <i>SQL injektáži</i>] a případně i proti syntaktickým chybám, pokud je identifikátor nevhodně navržen (např. obsahuje mezery, tečky a pod.). 

<pre>postgres=# SELECT quote_ident('aaaaa');
 quote_ident 
-------------
 aaaaa
(1 row)

postgres=# SELECT quote_ident('aaa''aa');
 quote_ident 
-------------
 "aaa'aa"
(1 row)

postgres=# SELECT quote_ident('aaa aa');
 quote_ident 
-------------
 "aaa aa"
(1 row)</pre>

Bohužel funkce quote_ident si neporadí se schématy. Viz výsledek volání funkce,

<pre>postgres=# SELECT quote_ident('tabulka.schema');
   quote_ident    
------------------
 "tabulka.schema"
(1 row)</pre>

který je nepoužitelný. Korektní identifikátor je v tomto případě "tabulka"."schema". Řešením, které ovšem není 100% (má problémy s tečkou uvnitř identifikátoru), je transformace do pole, a aplikace funkce quote_ident na každý prvek pole.

<pre>CREATE OR REPLACE FUNCTION quote_schema_ident(text) 
RETURNS text AS $$ 
SELECT array_to_string(ARRAY(SELECT quote_ident(unnest(string_to_array($1,'.')))),'.') 
$$ LANGUAGE sql;

postgres=# select quote_schema_ident('hloupy nazev schematu.tabulka');
       quote_schema_ident        
---------------------------------
 "hloupy nazev schematu".tabulka
(1 row)</pre>

Přetypováním na typ <i>regclass</i> lze jednoduše ověřit identifikátor tabulky. To je poměrně snadný způsob odhalení pokusů o SQL injektáž. Tím můžeme předejít standardním chybovým hlášením poskytujícím útočníkům  další cenné informace:

<pre>postgres=# \dt
             List of relations
 Schema |       Name       | Type  | Owner 
--------+------------------+-------+-------
 public | jmena            | table | pavel
 public | log              | table | pavel
 public | prefixes         | table | pavel
 public | test             | table | pavel
(12 rows)

postgres=# SELECT 'omega'::regclass;
 regclass 
----------
 omega
(1 row)

postgres=# SELECT 'omega a'::regclass;
ERROR:  invalid name syntax
LINE 1: SELECT 'omega a'::regclass;
               ^
postgres=# SELECT 'omegaa'::regclass;
ERROR:  relation "omegaa" does not exist
LINE 1: SELECT 'omegaa'::regclass;
               ^</pre>
S ověřováním validity SQL identifikátorů a s použitím klauzule [http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN <i>USING</i>] mohou být naše dynamické SQL příkazy neprůstřelné.

==Pole, tabulka, pole==
Seznam funkcí pro práci s poli je poměrně omezený - nicméně potřebné funkce si můžeme jednoduše napsat sami. Základní strategie je převod pole na tabulku, provedení určité množinové operace a převod tabulky zpět na pole. Příklad: Zrušení duplicit v poli. V PostgreSQL neexistuje funkce, která by rušila duplicitní prvky pole. Ovšem příkaz <i>SELECT</i> podporuje klauzuli <i>DISTINCT</i>, která zajistí výpis pouze unikátních záznamů. Jsme na stopě:

<pre>CREATE OR REPLACE FUNCTION array_distinct(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;</pre>

Datový typ <i>anyarray</i> představuje libovolné pole. Jedná se o tzv. <i>polymorfní typ</i>. V okamžiku volání funkce se polymorfní typ nahradí skutečným typem, podle typu hodnoty parametru (tak trochu jako templates v C++). Další příklad - spojení dvou polí:

<pre>CREATE OR REPLACE FUNCTION array_union(anyarray, anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT unnest($1) UNION ALL unnest($2))
$$ LANGUAGE sql;</pre>

Nativní implementace spojení polí v C bude ještě o něco rychlejší, k ale tomu potřebujeme hodně dobré znalosti PostgreSQL a C. Implementace v SQL je rychlostně v pohodě. Jednak je řádově rychlejší než implementace v PL/pgSQL a hlavně, hrdlem databázových operací je přístup na disk - CPU se fláká. Další příklad - dohledání prvku v poli:

<pre>CREATE OR REPLACE FUNCTION indexof(anyarray, anyelement, pos int = NULL)
RETURNS int AS $$
SELECT i 
   FROM generate_subscripts($1,1) g(i)
  WHERE $1[i] = $2 
    AND i >= COALESCE($1, array_lower($1,1))
$$ LANGUAGE sql;

postgres=# SELECT indexof(array[1,3,4,2,3,4,1],1,2);
 indexof 
---------
       7
(1 row)

postgres=# SELECT indexof(array[1,3,4,2,3,4,1],1);
 indexof 
---------
       1
(1 row)

postgres=# SELECT indexof(array[1,3,4,2,3,4,1],2);
 indexof 
---------
       4
(1 row)

postgres=# SELECT indexof(array[1,3,4,2,3,4,1],1,2);
 indexof 
---------
       7
(1 row)</pre>

Nejčastějším operací je seřazení pole:

<pre>CREATE OR REPLACE FUNCTION array_sort(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT unnest($1) ORDER BY 1)
$$ LANGUAGE sql;</pre>

Pole převedeme na jednosloupcovou tabulku, necháme seřadit podle prvního sloupce a výsledek převedeme zpět na pole.  Díky tomu, že se použijí interní rutiny pro řazení (<i>quick sort</i>) je funkce array_sort rychlá i pro velmi velká pole (nad 100 000 prvků):

<pre>-- samotné generovaní pole o velikosti 100 000 prvků
postgres=# SELECT ARRAY(SELECT random()*10000 FROM generate_series(1,100000));
                                                                                                    
----------------------------------------------------------------------------------------------------
 {2729.45704869926,13.0388513207436,2540.07804207504,5272.97182939947,270.577119663358,4648.89997150...
(1 row)

Time: 339,738 ms

-- sort
postgres=# SELECT array_sort(ARRAY(SELECT random()*10000 FROM generate_series(1,100000)));
                                                                                                    
----------------------------------------------------------------------------------------------------
 {0.00012572854757309,0.16817357391119,0.260430388152599,0.391206704080105,0.494923442602158,0.69868....
(1 row)

Time: 560,945 ms</pre>

==Pole a statistické funkce==
Určení běžných statistik jako je <i>průměr</i>, <i>minimum</i>, <i>maximum</i> je v SQL díky vestavěným agregačním funkcím jednoduché. Problematické jsou statistiky založené na pozici v seřazeném souboru dat - <i>kvantily</i>, <i>percentily</i> a například <i>medián</i>. ANSI SQL 2001 obsahuje funkci [http://www.postgresql.org/docs/8.4/interactive/functions-window.html <i>row_number</i>]. Tato funkce se nyní objevuje i v PostgreSQL - konkrétně ve verzi 8.4. Určení mediánu pak není problém ([http://www.simple-talk.com/sql/t-sql-programming/median-workbench/ metoda <i>Joe Celka</i>]):

<pre>CREATE TABLE  x(a integer);
INSERT INTO x SELECT (random()*10000)::int FROM generate_series(1,10000);

postgres=# SELECT avg(a)::float
              FROM (SELECT a, row_number() OVER (ORDER BY a asc) AS hi,
                              count(*) OVER () + 1 - row_number() OVER (ORDER BY a) AS lo
                       FROM x) qs
             WHERE hi IN (lo-1,lo,lo+1);
 avg  
------
 4936
(1 row)

Time: 112,469 ms</pre>

Ve starších verzích PostgreSQL bylo několik možností:
<ol type="a">
<li>použít SELF JOIN alchymii 
<li>použít kurzor
<li>použít pole
</ol>
Varianta b bude nejrychlejší, varianta c naopak nejjednodušší a k tomu řádově rychlejší než varianta a. Funkce pro určení mediánu z pole může vypadat následovně:

<pre>CREATE OR REPLACE FUNCTION array_median(float[]) 
RETURNS float AS $$ 
SELECT ((a.v[l/2+1] + a.v[(l+1)/2])/2.0) 
   FROM (SELECT ARRAY(SELECT unnest($1) ORDER BY 1), 
                array_upper($1,1) - array_lower($1,1) + 1) a(v,l)
$$ LANGUAGE sql;

postgres=# select array_median(ARRAY(SELECT a FROM x));
 array_median
---------------
          4936
(1 row)

Time: 68,625 ms</pre>

Pokud bychom dopředu znali velikost tabulky, pak medián určíme dotazem:

<pre>postgres=# SELECT avg(a)::float FROM (SELECT a FROM x ORDER BY 1 OFFSET 5000-1 LIMIT 2) s;
 avg  
------
 4936
(1 row)

Time: 22,212 ms</pre>

==Pole a variadické funkce==
[http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html <i>Variadické funkce</i>] jsou funkce, které nemají pevný počet parametrů. V PostgreSQL je několik takových funkcí  - <i>coalesce</i>, <i>greatest</i>, <i>least</i>. Počínaje verzí 8.4 můžeme navrhovat vlastní variadické funkce. A jelikož se variadické parametry předávají funkci jako pole, můžeme uplatnit veškeré výše uvedené postupy. Začnu ukázkou dvou jednoduchých funkcí <i>concat</i> a <i>myleast</i>:

<pre>CREATE OR REPLACE FUNCTION concat(VARIADIC str text[])
RETURNS text AS $$
SELECT array_to_string($1,'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION concat_ws(separator text, VARIADIC str text[])
RETURNS text AS $$
SELECT array_to_string($2,$1)
$$ LANGUAGE sql;

postgres=# SELECT concat_ws('.', 'kix','fsv','cvut','cz');
    concat_ws    
-----------------
 kix.fsv.cvut.cz
(1 row)

postgres=# SELECT concat_ws('.','cvut','cz');
 concat_ws 
-----------
 cvut.cz
(1 row)

CREATE OR REPLACE FUNCTION myleast(VARIADIC anyarray) 
RETURNS anyelement AS $$
SELECT min(v) FROM unnest($1) u(v)
$$ LANGUAGE sql;

postgres=# SELECT myleast(1,2,3,-1);
 myleast 
---------
      -1
(1 row)

postgres=# SELECT myleast('A'::text,'B','C');
 myleast 
---------
 A
(1 row)</pre>

V MySQL je jedna docela zajímavá funkce [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field <i>field</i>]. Vrací pořadové číslo parametru, který se shoduje se zadanou hodnotou. Lze ji použít v klauzuli ORDER BY pro explicitní určení pořadí:

<pre>mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0

CREATE OR REPLACE FUNCTION field(str text, VARIADIC strn text[]) 
RETURNS int AS $$
SELECT i 
   FROM generate_subscripts($2,1) g(i) 
  WHERE $2[i] = $1 
  UNION ALL 
  SELECT 0 
  LIMIT 1$$ LANGUAGE sql;</pre>

Aby byla implementace úplná, je třeba ještě přidat číselnou variantu:
<pre>CREATE OR REPLACE FUNCTION field(str numeric, VARIADIC strn numeric[]) 
RETURNS int AS $$
SELECT i 
   FROM generate_subscripts($2,1) g(i) 
  WHERE $2[i] = $1 
  UNION ALL 
  SELECT 0 
  LIMIT 1$$ LANGUAGE sql;</pre>

Spuštěním příkladů z dokumentace MySQL si můžeme ověřit funkčnost naší funkce field:

<pre>postgres=# SELECT field('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
 field 
-------
     2
(1 row)

postgres=# SELECT field('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
 field 
-------
     0
(1 row)</pre>

Funkce <i>elt</i> je komplementární k funkci field. Vrací n-tý parametr funkce:

<pre>mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'</pre>

Implementace této funkce je krásně triviální:
<pre>CREATE OR REPLACE FUNCTION elt(n int, VARIADIC strn text[]) 
RETURNS text AS $$ 
SELECT $2[$1]
$$ LANGUAGE sql;

postgres=# SELECT elt(1, 'ej', 'Heja', 'hej', 'foo');
-----
 ej
(1 row)

postgres=# SELECT elt(4, 'ej', 'Heja', 'hej', 'foo');
 elt 
-----
 foo
(1 row)</pre>
==Tabulka jako pole==
Pomocí polí můžeme simulovat vlastnost jiných databází, která se označuje jako ''nested tables''. Ačkoliv nested tables vypadají lákavě, nepoužívejte je pro ukládání dat! Připravíte se o optimalizaci, indexy.
<pre>
postgres=# create table f(a int, b int, c int);
CREATE TABL
postgres=# create table ff(v f[]);
CREATE TABLE
postgres=# insert into  f values(10,20,30),(40,50,60);
INSERT 0 2
postgres=# insert into ff select array(select row(a,b,c)::f from f);
INSERT 0 1
postgres=# select * from ff;
              v
-----------------------------
 {"(10,20,30)","(40,50,60)"}
(1 row)

postgres=# select unnest(v) from ff;
   unnest
------------
 (10,20,30)
 (40,50,60)
(2 rows)

postgres=# select (unnest(v)).* from ff;
 a  | b  | c
----+----+----
 10 | 20 | 30
 40 | 50 | 60
(2 rows)
</pre>

==Odkazy==
Některé ze zde uvedených příkladů můžeme najít na webu [http://www.postgres.cz/index.php/SQL_Triky]. Další příklady a další inspiraci najdeme v archivu fragmentů kódu [http://wiki.postgresql.org/wiki/Category:Snippets].