ARCHIV

PostgreSQL (18) - Datový typ pole II

V minulém dílu seriálu jsme se seznámili s datovým typem pole, který PostgreSQL ovládá a v tomto díle je na čase podívat se podrobněji na práci s tímto typem, zejména pak z hlediska vestavěných obslužných funkcí.

8.2.2006 06:00 | MaReK Olšavský | Články autora | přečteno 13177×

V příkazu SELECT můžete pole použít nejen v části WHERE, ale i při běžném výběru sloupců, které mají být z datové věty vybrány. I v tomto případě je zápis stejný jako v části WHERE, do hranatých závorek se píše buď přímo prvek, který chcete nebo rozsah oddělený dvojtečkou ve tvaru [dolni_index:horni_index]. Rozsahem lze zapsat i výběr jediného prvku z pole, ale v tom případě jsou oba indexy stejné.

Pro příklady tohoto dílu bude použita databázová tabulka, která byla vytvořena v minulém díle. Potřebná data jsou v počátečním stavu tato:

INSERT INTO employees_pays VALUES (23, '{0.00,15075.00,11630.00}',
 '{0.00,0.00,500.00}', '{0.00,1200.00,973.00}',
 '{0.00,0.00,0.00,8000.00}', '{}', '2005-09-10');

INSERT INTO employees_pays VALUES (1, '{0.00,15075.00,11630.00}', 
'{0.00,0.00,500.00}','{{0.00,1200.00,973.00},
 {0.00,994.00,199.00},{0.00,0.00,12.30}}',
 '{0.00,0.00,0.00,8000.00}', NULL, '2005-09-10');

INSERT INTO employees_pays VALUES (2, '{152.00,16.50}', '{255.00,0.00}',
 '{{175.00,60.00},{0.00,0.00},{1200.00,1250.00}}',
 '{0.00,8900.00,9950.00,9945.00}', '{7500.00}', '2006-01-12');

Pro výběry poskytuje PostgreSQL několik zajímavých funkcí, které práci s polem usnadní. Pokud je zapotřebí zjistit aktuální rozměry pole pro každý řádek extra (jak bylo napsáno minule, zatím v PgSQL není nijak vyžadováno dodržování rozměrů polí), poslouží funkce array_dims. Její použití je jednoduché, postačí pouze název sloupečku jehož rozměry jsou potřeba získat, předat jako parametr této funkce. Návratová hodnota bude typu text, například:

SELECT ARRAY_DIMS(last3_tax_insurance), employee_id FROM employees_pays;


array_dims | employee_id
-----------+-------------
 [1:3]     |         23
 [1:3][1:3]|          1
 [1:3][1:2]|          2

(3 rows)

Pokud je zapotřebí vybrat první, nebo poslední prvek z pole, přičemž nejsou známy rozměry (tj. počet již zadaných prvků), poslouží 2 funkce - array_lower, pro získání indexu prvního prvku v poli a array_upper, pro získání indexu posledního prvku v poli. První parametr této funkce je jméno pole a druhý je dimenze (v případě vícerozměrného pole), jejíž minimální/maximální je zapotřebí. Opět bude vše mnohem jasnější z příkladů: 

select array_lower(last3_tax_insurance), employee_id from employees_pays;
 --chyba, neni uvedena dimenze, jejiz dolni indexy potrebujeme

select array_lower(last3_tax_insurance,1), employee_id from employees_pays;
 -- spravne a uvidite dolni indexy z pole last3_tax_insurance

select array_upper(last3_tax_insurance,1), employee_id from employees_pays;
 -- horni indexy prvniho rozmeru pole

last3_tax_insurance[array_upper(last3_tax_insurance,1)], employee_id from
 employees_pays;
 -- vyber hodnot s nejvyssimi indexy.

Výše uvedené funkce, zejména array_upper, mohou posloužit, když je zapotřebí při updatu datové věty přidat další hodnotu (s indexem vyšším, než je nejvyšší index pole). Toto přidání lze provést jen s polem, které má jedinou dimenzi, alespoň zatím, ve verzi 8.x (včetně nejnovější 8.1). Sekvence pro přidání hodnoty do pole je tato: UPDATE employees_pays SET last_4_qyears[array_upper(last_4_qyears,1)+1] = 25600 WHERE employee_id=23;. Drobný problém nastává, jestliže zatím v poli není žádná hodnota, protože v tom případě funkce array_upper nevrací hodnotu 0, ale NULL, k tomu se těžko přičítá a je třeba PgSQL trochu pomoci, třeba takto: UPDATE employees_pays SET years_avg[COALESCE(array_upper(years_avg,0),0)+1] = 152.00 WHERE  employee_id=23; , funkcí COALESCE se zatím nezabývejte, podmínky v SQL budou popsány ve velmi blízkém dílu.

Při výstupu dat z pole nebo jejich zadávání z externího programu se často hodí funkce pro předání ne ve tvaru pole, ale ve formátu s vlastními oddělovači, například '|' (svislítko, roura). PgSQL přímo nabízí dvojici funkcí array_to_string(pole, oddělovač), která z pole udělá řetězec oddělený Vaším zvoleným oddělovačem a string_to_array(řetězec, řetězec), která první řetězec rozdělí při použití druhého řetězce, jako oddělovače. Oddělovač nemusí být jen jednoznakový, ale sekvence :-) také poslouží.

-- preformatovany vystup z pole do retezce 
SELECT array_to_string(last_4_qyears,'|') FROM employees_pays;
 array_to_string 
-----------------------------------------
 0.00|0.00|0.00|8000.00
 0.00|8900.00|9950.00|9945.00
 0.00|0.00|0.00|8000.00|9500.50|25600.00

-- vystup pole bez formatovany
SELECT last_4_qyears FROM employees_pays;
 last_4_qyears 
-------------------------------------------
 {0.00,0.00,0.00,8000.00}
 {0.00,8900.00,9950.00,9945.00}
 {0.00,0.00,0.00,8000.00,9500.50,25600.00}

PgSQL poskytuje několik funkcí a operátorů pro spojování několika polí dohromady. První z nich je dvojice array_append a array_prepend pro připojení (skalární) hodnoty k poli. Array_append připojí tuto proměnnou k poli zezadu a array_prepend zepředu, paramtery jsou pole a prvek k připojení v pořadí, jak se spojují. Pole se dají spojit pomocí funkce array_cat, kde parametry jsou dvě pole určená ke spojení a výstupem je nové, již spojené pole. Stejného efektu spojení polí i prvků lze docílit operátorem '||'.

-- pripojeni skalaru za pole pomoci funkce
SELECT array_append(last_4_qyears,500.00) FROM employees_pays;
 array_append 
--------------------------------------------------
 {0.00,0.00,0.00,8000.00,500.00}
 {0.00,8900.00,9950.00,9945.00,500.00}
 {0.00,0.00,0.00,8000.00,9500.50,25600.00,500.00}

-- pripojeni pole za skalar pomoci operatoru
SELECT 1234.56 || last_4_qyears FROM employees_pays;
 ?column? 
---------------------------------------------------------
 [0:4]={1234.56,0.00,0.00,0.00,8000.00}
 [0:4]={1234.56,0.00,8900.00,9950.00,9945.00}
 [0:6]={1234.56,0.00,0.00,0.00,8000.00,9500.50,25600.00}

-- spojeni dvou poli operatorem
SELECT last_3_months || last_4_qyears FROM employees_pays;
 ?column? 
------------------------------------------------------------------
 {0.00,15075.00,11630.00,0.00,0.00,0.00,8000.00}
 {152.00,16.50,0.00,8900.00,9950.00,9945.00}
 {0.00,15075.00,11630.00,0.00,0.00,0.00,8000.00,9500.50,25600.00}

Pro porovnání dvou polí lze použít běžné porovnávací operátory <, >, <=, >=, funguje zde takzvané slovníkové řazení, tj. {1,3,2}>{1,2,3}.

Závěr

Tímto druhým dílem jsme uzavřeli téma polí. Pokud Vás zajímá k čemu pole v databázi použít, tak to jsou právě ty průměry a měsíční průběhy mezd, nebo třeba známky hodnotící studenty ;-). V příštím díle se podíváme na strukturované datové typy.

Verze pro tisk