Pole v PostgreSQL

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

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

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[] |

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: 

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)

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

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)

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 csv. Příkaz 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: 

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)

případně v kombinaci s příkazem COPY: 

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"

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

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)

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. 

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)

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 PL/pgSQL, Perlu, Pythonu, v jazyce C, a nebo v jazyce SQL: 

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)

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. 

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)

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: 

/* 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)

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

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)

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

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

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. 

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)

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: 

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;
               ^

S ověřováním validity SQL identifikátorů a s použitím klauzule 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ě: 

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

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

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

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: 

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)

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

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

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ů): 

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

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 row_number. Tato funkce se nyní objevuje i v PostgreSQL - konkrétně ve verzi 8.4. Určení mediánu pak není problém (metoda Joe Celka): 

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

Ve starších verzích PostgreSQL bylo několik možností:

  1. použít SELF JOIN alchymii
  2. použít kurzor
  3. použít pole

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ě: 

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

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

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

Pole a variadické funkce

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: 

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)

V MySQL je jedna docela zajímavá funkce 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í: 

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;

Aby byla implementace úplná, je třeba ještě přidat číselnou variantu: 

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;

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

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)

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

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

Implementace této funkce je krásně triviální: 

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)

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. 

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)

Odkazy

Některé ze zde uvedených příkladů můžeme najít na webu [1]. Další příklady a další inspiraci najdeme v archivu fragmentů kódu [2].

Citováno z „http://postgres.cz/index.php?title=Pole_v_PostgreSQL&oldid=463“