PostgreSQL - zabawa z tablicami

dodał: ddarko (2009-12-27 12:23),   tagi: postgresql

PostgreSQL IMHO jest najlepszą i najprzyjemniejszą w obsłudze darmową bazą danych. Już dawno temu zostawił w tyle swojego konkurenta MySQL. W tym artykule chciałem przybliżyć temat tablic w postgresie.

Na początek tworzymy nową bazę „test”: 
$ createdb -U postgres test

Łączymy się z nową bazą przy pomocy standardowego klienta: 
$ psql -U postgres test

Tworzymy przykładową tabelę „tab” na dane typu teksotowego (można robić tablice dowolnych typów): 
test=# CREATE TABLE tab (id serial, t text[]);

Sprawdzamy czy wszystko zostało utworzone poprawnie: 
test=# \d
             List of relations
 Schema |    Name    |   Type   |  Owner   
--------+------------+----------+----------
 public | tab        | table    | postgres
 public | tab_id_seq | sequence | postgres
(2 rows)

test=# \d tab
                         Table "public.tab"
 Column |  Type   |                    Modifiers                     
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('tab_id_seq'::regclass)
 t      | text[]  |

Dodajemy kilka przykładowych wpisów: 
test=# INSERT INTO tab (t) VALUES ('{ddarko}');
INSERT 0 1
test=# INSERT INTO tab (t) VALUES ('{ddarko,karol,ola}');
INSERT 0 1
test=# INSERT INTO tab (t) VALUES ('{ania,michał,zosia}');
INSERT 0 1

Sprawdzamy czy dane zostały dodane pomyślnie: 
test=# SELECT * FROM tab;
 id |          t          
----+---------------------
  1 | {ddarko}
  2 | {ddarko,karol,ola}
  3 | {ania,michał,zosia}
(3 rows)

Wszystko wygląda fajnie :] Co teraz możemy z tym zrobić ?!
Np. chcemy wydobyć drugi element z tablicy dla każdego wiersza: 
test=# SELECT t[2] FROM tab;
   t    
--------
 
 karol
 michał
(3 rows)
Jak widać pierwszy wiersz nie ma drugiego elementu i dostaliśmy NULL, w pozostałych przypadkach jest to jakiś pojedynczy wyraz.

Przykładowo: chcemy uzyskać id dla wpisów, które zawierają słowo „ddarko”. 
test=# SELECT id FROM tab WHERE 'ddarko' = ANY (t);

Często napotykałem na potrzebę skorzystania z ILIKE do szukania wewnątrz tablicy.
Chciało by się napisać: 
test=# SELECT id FROM tab WHERE 'DD%' ILIKE any (t);

niestety nie działa to poprawnie :( , aby uzyskać pożądany efekt stworzyłem własny operator: 
test=# create function rilike(text,text) returns bool as 'select $2 ilike $1' language sql strict immutable;
test=# create operator ~~~ (procedure = rilike, leftarg = text, rightarg = text, commutator = ~~);

Po wykonaniu dwuch powyższych linijek uzyskujemy dostęp do nowego operatora ~~~, przykład jego wykorzystania: 
test=# SELECT id FROM tab WHERE 'DD%' ~~~ ANY (t);

Gdy chcemy odszukać wiersze zawierające literkę np. „ł”: 
test=# SELECT * FROM tab WHERE '%ł%' ~~~ ANY (t);
 id |          t          
----+---------------------
  3 | {ania,michał,zosia}
(1 row)

Jeżeli chcemy odszukać wiersze nie zawierające litery np. „ł”: 
test=# SELECT * FROM tab WHERE NOT '%ł%' ~~~ ANY (t);
 id |         t          
----+--------------------
  1 | {ddarko}
  2 | {ddarko,karol,ola}
(2 rows)

Kolejny przykład:
Mamy dwa wyrazy np.: „ania”, „zosia” i chcemy znaleźć wiersze, które zawierają oba wyrazy. 
test=# select * from tab where '{ania,zosia}' <@ t;
 id |          t          
----+---------------------
  3 | {ania,michał,zosia}
(1 row)

Jeżeli chcemy znaleźć wiersze, które zawierają dowolny wyraz z podanej przez nas tablicy (np.: „ania”, „ola”): 
test=# select * from tab where '{ania,ola}' && t;
 id |          t          
----+---------------------
  2 | {ddarko,karol,ola}
  3 | {ania,michał,zosia}
(2 rows)

Niby wszystko dobrze, jednak nie wiemy, który wyraz został odnaleziony w danym wierszu.
Ponieważ w standardzie nie ma funkcji, która to realizuje musimy stworzyć taką funkcję sami: 
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION array_common_elements (ANYarray, ANYarray) RETURNS ANYarray AS
$$
	DECLARE
		out ALIAS FOR $0;
	BEGIN
		FOR idx IN array_lower($1, 1)..array_upper($1, 1) LOOP
			IF $1[idx] = any($2) THEN
				out = array_append(out, $1[idx]);
			END IF;
		END LOOP;
	RETURN out;
	END;
$$
LANGUAGE plpgsql;

i tutaj zapytanie rozwiązujące przykładowy problem: 
test=# select id, array_common_elements('{ania,ola}',t) from tab where array_common_elements('{ania,ola}',t) is not null;
 id | array_common_elements 
----+-----------------------
  2 | {ola}
  3 | {ania}
(2 rows)


Spis operatorów dostępnych na tablicach:

Operator Opis Przykład wykorzystania
= równość 
SELECT array[1.1,2.1,3.1]::int[] = array[1,2,3];
 t
<> różność 
SELECT array[1,2,3] <> array[1,2,4];
 t
< mniej niż 
SELECT array[1,2,3] < array[1,2,4];
 t
> więcej niż 
SELECT array[1,4,3] > array[1,2,4];
 t
<= mniej niż lub równość 
SELECT array[1,2,3] <= array[1,2,3];
 t
>= więcej niż lub równość 
SELECT array[1,4,3] >= array[1,4,3];
 t
@> zawieranie (cała tablica zawiera się w innej) 
SELECT array[1,4,3] @> array[3,1];
 t
<@ zawarte w 
SELECT array[2,7] <@ array[1,7,4,2,6];
 t
&& część wspólna (co najmniej jeden element wspólny) 
SELECT array[1,4,3] && array[2,1];
 t
|| łączenie połączenie dwuch tablic:
SELECT array[1,2,3] || array[4,5,6];
{1,2,3,4,5,6}
dodanie elementu do tablicy:
SELECT 3 || array[4,5,6];
{3,4,5,6}

SELECT array[4,5,6] || 7;
{4,5,6,7}


Spis funkcji dostępnych na tablicach:

Funkcja Wynik funkcji Opis Przykład wykorzystania
array_append(anyarray, anyelement) anyarray dodaje element na koniec tablicy 
SELECT array_append(array[1,2], 3);
{1,2,3}
array_cat(anyarray, anyarray) anyarray łączy dwie tablice ze sobą 
SELECT array_cat(array[1,2,3], array[4,5]);
{1,2,3,4,5}
array_dims(anyarray) text zwraca rozmiar/wielkość tablicy 
SELECT array_dims(array[[1,2,3], [4,5,6]]);
[1:2][1:3]
array_lower(anyarray, int) int zwraca mniejszy wymiar tablicy 
array_lower('[0:2]={1,2,3}'::int[], 1);
0
array_prepend(anyelement, anyarray) anyarray dodanie elementu na początek tablicy 
SELECT array_prepend(1, array[2,3]);
{1,2,3}
array_to_string(anyarray, text) text łączenie elementów tablicy przy pomocy ciągu znaków 
SELECT array_to_string(array[1, 2, 3], '~^~');
1~^~2~^~3
array_upper(anyarray, int) int zwraca największy wymiar tablicy 
SELECT array_upper(array[1,2,3,4], 1);
4
string_to_array(text, text) text[] zamiana ciągu znaków na tablicę, dzielenie po podanym ciagu znaków 
SELECT string_to_array('xx~^~yy~^~zz', '~^~');
{xx,yy,zz}




Spis ciekawych sztuczek: tutaj.