ARCHIV

PostgreSQL (17) - Datový typ pole

Tímto dílem začíná logicky pokročilejší část seriálu. V následujících několika dílech budou probrány složitější datové typy.

26.10.2005 07:00 | MaReK Olšavský | Články autora | přečteno 15070×

Pole

Pole jako datová struktura se vyskytuje snad ve všech programovacích jazycích. Definuje se jako n-tice (v případě jednorozměrného pole) hodnot daného datového typu. Pole může být jedno, ale i více rozměrné, jeho jednotlivé prvky se zpřístupňují pomocí indexů (klíčů), jenž nemají nic společného s indexy a klíči ve smyslu databází, které jsou většinou číselné. Některé programovací jazyky dovolují vytvářet asociativní pole, kdy indexace nemusí být již jen číselná, ale lze použít názvy složek pole, podobně, jako názvy sloupců databázových serverů. Pole mohou být jedno, dvou, tří i více rozměrná, záleží to jen na konkrétní podobě. Jednorozměrná pole (bez ohledu na počet prvků) jsou v mnoha programátorských příručkách pojmenovány jako vektory, dvou a více rozměrná pole jako matice.

PostgreSQL server umožňuje definovat sloupce dat jako pole, ať již jednorozměrné, nebo vícerozměrné, na rozdíl od řady programovacích jazyků není třeba definovat (alokovat), nebo měnit (realokovat) jeho velikost. Jak bylo napsáno, není nutné určit počet prvků v poli, ale je totožné, jestliže je tento údaj v době psaní aplikace a návrhu databáze znám. Například je stěží určitelné, jak veliké pole je třeba pro průběžné ukládání ročních příjmů u jednoho zaměstnavatele (nejzažší varianta by mohla nástup v 18 letech a odchod při dosažení důchodového věku, ale ta druhá hranice je průběžně zvyšována), ale rozměr pole pro průměry mzdy za poslední 4 čtvrtletí je nasnadě definovatelný, jako 4. A aby to nebylo nakonec zcela snadné a jednoduché, tak pokud je deklarována velikost pole, lze ji bez potíží překročit, žádná data nebudou ztracena a budou i korektně vrácena, čiže informace o velikosti je zde uvedena spíše pouze jako orientační pro vývojáře (btw: není vyloučeno, že toto v budoucích verzích nezmění a definice velikosti bude nepřekročitelná, v původní dokumentaci je napsáno "...the current implementation does not enforce...").

PostgreSQL podporuje při tvorbě polí v tabulkách, kromě své syntaxe zmíněné výše, i syntaxi, která je standardem v normě SLQ:1999, tj. za název a typ sloupce se nezapíší pouze hranaté závorky, ale připíše se i klíčové slovo ARRAY, takže definice jednoho z řádků v níže uvedeném příkladu by vypadala takto: last_3_months_decimal(12,2) ARRAY[3],.

To, že se jedná o pole ve sloupci definice tabulky s poli, se PostgreSQL serveru určí takto:

CREATE TABLE employees_pays(
 employee_id serial, --jen reference na zamestnance
 last_3_months decimal(12,2)[3], --mzda za posledni 3 mesice
 last3_health_benefit decimal(12,2)[3], --nemocenska
 last3_tax_insurance decimal(12,2)[3][3][3], -- dan, zdrav. a soc. poj.
 last_4_qyears decimal(12,2)[4], --mzdove prumery za posl. 4 ctvrtleti
 years_avg decimal(12,2)[], --rocni prumery mezd
 last_pay date --datum posledni mzdy
);

Zadávání dat do tabulek je velice jednoduché. Hodnoty, které patří do pole se pouze uzavřou do složených závorek, oddělené čárkami. Je potřebné věnovat pozornost tomu, aby pro vícerozměrná pole byla zadána alespoň prázdná hodnota (tj. prázdné závorky). Pokud se do jednoho sloupce databáze zadávají hodnoty vícerozměrných polí, každý rozměr se uzavírá do složených závorek, přižemž všechny dimenze patřičného sloupce jsou v "obalujících" složených závorkách. Hodnoty se zadávají, tak jak je obvyklé, tj. číselné hodnoty jen číslem, datumy, řetězce a další do uvozovek a geometrické hodnoty tak, jak to dovolují jejich jednotlivé typy.

Zadání dat (platné a s chybou) vypadá takto:

--spravne zadano, data nejsou korektne dle zakona
--jen ukazka
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,994.00},{}}',
	'{0.00,0.00,0.00,8000.00}','{}','2005-09-10');
--ukazka s chybou
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,994.00}}',
	'{0.00,0.00,0.00,8000.00}','{}','2005-09-10');
--Ukazka ukladani podle SQL:1999 standardu
INSERT INTO employees_pays VALUES(23,
 ARRAY[0.00,15075.00,11630.00],ARRAY[0.00,0.00,500.00],
	ARRAY[[0.00,1200.00,973.00],[0.00,994.00],[]],
	ARRAY[0.00,0.00,0.00,8000.00],ARRAY[],'2005-09-10');

Uložená data se vybírají poměrně jednoduše příkazem SELECT. Přístup k učitému prvku pole se realizuje stejným způsobem, jako ve většině ostatních programovacích jazyků, tj. index prvku se zapíše do hranatých závorek. Pokud je potřeba vybrat z pole hodnoty mezi dvěma indexy, zapíšou se tyto dva indexy oddělené od sebe dvoutečkou, je-li potřebné vybrat hodnoty Následující příklady dannou problematiku osvětlí poněkud lépe a snadněji, než teorie:

--vypis vseho
SELECT * FROM emlpoyees_pays;
--vyber poslednich ctvrtletnich prumeru
SELECT last_4_qyears[3-4] FROM employees_pays;

--vyber prvni a posledni hodnoty z pole prumeru
SELECT last_4_qyears[3:4] FROM employees_pays;

--vyber tech, kde je stejny prumer za prvni 2 ctvrtleti
SELECT * FROM empoyees_pays WHERE
 last_4_qyears[1]=last_4_qyears[2];

Při výběru hodnot by mohl nastat stav, že je potřebné vybrat hodnoty, kdy některá z položek pole, případně všechny splňují podmínku. Pro tuto variantu výběru existují dva operátory: ANY a ALL. Jejich zápis je takovýto:

--výběr kde kterykoliv prumer je vetsi, nez 7000
SELECT * FROM employees_pays WHERE
ANY(last_4_qyears)>7000;

-- totez bez any
SELECT * FROM employees_pays WHERE
last_4_qyears[1]>7000 OR last_4_qyears[2]>7000
OR last_4_qyears[3]>7000 OR
last_4_qyears[4]>7000;

 --vyber, kde vsechny jsou vetsi, nez 7000
SELECT * FROM employees_pays WHERE 
ALL(last_4_qyears)>7000
--Tomuto by byl ekvivalenti dlouhy priklad zhora, ale
--misto OR by tam bylo AND

Kromě vkládání dat, nebo jejich výběru je někdy třeba provádět také jejich modifikaci. Smazání (SQL příkaz DELETE) je snadné a zápis je stejný, jako při práci bez polí. UPDATE může měnit jak hodnotu celého pole, tak partikulárně pouze některé členy. Pro obojí se používá stejná syntaxe, jako pro výběr, tj. přes indexy pole.

--update celeho pole
UPDATE employees_pays SET last_3_months = '{10000.00,9850.00,11230.00}'
 WHERE employee_id = 5;

--update celeho pole za pouziti ARRAY
UPDATE employees_pays SET last_3_months = ARRAY [10000.00,9850.00,11230.00]
 WHERE employee_id = 5;

--update 2 a 3 hodnoty
UPDATE employees_pays SET last_3_months[2:3] = '{9850.00,11230.00}'
 WHERE employee_id = 5;

Závěr

Práce s poli má mnoho dalších možností, čiže tomuto tématu bude věnován ještě jeden (zhruba stejně dlouhý) díl. Protože na tomto serveru běží server i o konkurenční databázi, zkoužel jsem najít podporu polí i pro MySQL, ale moc jsem v rychlosti neuspěl.

Verze pro tisk

DISKUZE pole x dalsi tabulka 26.10.2005 13:26 Karel Honzl   Re: pole x dalsi tabulka 26.10.2005 13:48 MaReK Olšavský   Re: pole x dalsi tabulka 26.10.2005 15:22 Tomas Konir     Re: pole x dalsi tabulka 26.10.2005 16:37 MaReK Olšavský Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.