imported>Pavel: /* Pole a dynamické SQL */

2009-09-15T14:36:28Z

Pole a dynamické SQL

Nová stránka

[[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 unnest. Agregační funkcí array_agg 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 string_to_array (transformuje řetězec na pole) a array_to_string (generuje řetězec z pole). Pokud na pole převádíme kompletní výsledek dotazu, můžeme použít konstruktor pole z poddotazu - ARRAY(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 COPY] 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 DELIMITER (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 unpack_domains. 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 PL/pgSQL], [http://www.postgresql.org/docs/8.4/interactive/plperl.html Perlu], [http://www.postgresql.org/docs/8.4/interactive/plpython.html Pythonu], v [http://www.postgres.cz/index.php/N%C3%A1vrh_a_realizace_UDF_v_c_pro_PostgreSQL jazyce C], 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 generate_subscripts 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 regexp_split_to_array 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 quote_ident. Tím zabezpečujeme své aplikace proti [http://en.wikipedia.org/wiki/SQL_injection SQL injektáž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 regclass 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 USING] 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 SELECT podporuje klauzuli DISTINCT, 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 anyarray představuje libovolné pole. Jedná se o tzv. polymorfní typ. 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í (quick sort) 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 průměr, minimum, maximum 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 - kvantily, percentily a například medián. ANSI SQL 2001 obsahuje funkci [http://www.postgresql.org/docs/8.4/interactive/functions-window.html row_number]. 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 Joe Celka]):

<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 Variadické funkce] jsou funkce, které nemají pevný počet parametrů. V PostgreSQL je několik takových funkcí - coalesce, greatest, least. 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í concat a myleast:

<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 field]. 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 elt 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].

Pole v PostgreSQL - Historie editací

imported>Pavel: /* Pole a dynamické SQL */