Массив или таблица?

Представим себе, что мы проектируем базу данных для ведения блога. В блоге есть сообщения, и нам хотелось бы сопоставлять им теги.

Традиционный подход состоит в том, что для тегов надо создать отдельную таблицу, например, так: 

=> CREATE TABLE posts(
    post_id integer PRIMARY KEY,
    message text
);

CREATE TABLE

=> CREATE TABLE tags(
    tag_id integer PRIMARY KEY,
    name text
);

CREATE TABLE

И теперь связываем сообщения и теги отношением многие-ко-многим через еще одну таблицу: 

=> CREATE TABLE posts_tags(
    post_id integer REFERENCES posts(post_id),
    tag_id integer REFERENCES tags(tag_id)
);

CREATE TABLE

Наполним таблицы тестовыми данными: 

=> INSERT INTO posts(post_id,message) VALUES
    (1, 'Перечитывал пейджер, много думал.'),
    (2, 'Это было уже весной и я отнес елку обратно.');

INSERT 0 2

=> INSERT INTO tags(tag_id,name) VALUES
    (1, 'былое и думы'), (2, 'технологии'), (3, 'семья');

INSERT 0 3

=> INSERT INTO posts_tags(post_id,tag_id) VALUES
    (1,1), (1,2), (2,1), (2,3);

INSERT 0 4

Теперь мы можем вывести сообщения и теги: 

=> SELECT p.message, t.name
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
ORDER BY p.post_id, t.name;

                   message                   |     name     
---------------------------------------------+--------------
 Перечитывал пейджер, много думал.           | былое и думы
 Перечитывал пейджер, много думал.           | технологии
 Это было уже весной и я отнес елку обратно. | былое и думы
 Это было уже весной и я отнес елку обратно. | семья
(4 rows)

Или чуть иначе - возможно удобнее получить массив тегов: 

=> SELECT p.message, array_agg(t.name ORDER BY t.name) tags
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.post_id
ORDER BY p.post_id;

                   message                   |            tags             
---------------------------------------------+-----------------------------
 Перечитывал пейджер, много думал.           | {"былое и думы",технологии}
 Это было уже весной и я отнес елку обратно. | {"былое и думы",семья}
(2 rows)

Можем найти все сообщения с определенным тегом: 

=> SELECT p.message
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name = 'былое и думы'
ORDER BY p.post_id;

                   message                   
---------------------------------------------
 Перечитывал пейджер, много думал.
 Это было уже весной и я отнес елку обратно.
(2 rows)

Может потребоваться найти все уникальные теги - это совсем просто: 

=> SELECT t.name
FROM tags t
ORDER BY t.name;

     name     
--------------
 былое и думы
 семья
 технологии
(3 rows)

Теперь попробуем подойти к задаче по-другому. Пусть теги будут представлены текстовым массивом прямо внутри таблицы сообщений. 

=> DROP TABLE posts_tags;

DROP TABLE

=> DROP TABLE tags;

DROP TABLE

=> ALTER TABLE posts ADD COLUMN tags text[];

ALTER TABLE

Теперь у нас нет идентификаторов тегов, но они нам не очень и нужны. 

=> UPDATE posts SET tags = '{"былое и думы","технологии"}' WHERE post_id = 1;

UPDATE 1

Или другой синтаксис: 

=> UPDATE posts SET tags = ARRAY['былое и думы','семья'] WHERE post_id = 2;

UPDATE 1

Вывод всех сообщений упростился: 

=> SELECT p.message, p.tags
FROM posts p
ORDER BY p.post_id;

                   message                   |            tags             
---------------------------------------------+-----------------------------
 Перечитывал пейджер, много думал.           | {"былое и думы",технологии}
 Это было уже весной и я отнес елку обратно. | {"былое и думы",семья}
(2 rows)

Сообщения с определенным тегом тоже легко найти (используем оператор пересечения &&): 

=> SELECT p.message
FROM posts p
WHERE p.tags && '{"былое и думы"}'
ORDER BY p.post_id;

                   message                   
---------------------------------------------
 Перечитывал пейджер, много думал.
 Это было уже весной и я отнес елку обратно.
(2 rows)

Эта операция может быть ускорена с помощью GIN-индекса, так что для такого запроса не придется перебирать всю таблицу сообщений.

А вот получить список тегов довольно сложно. Это требует разворачивания всех массивов тегов в большую таблицу (функция unnest) и поиск уникальных значений - тяжелая операция. 

=> SELECT unnest(p.tags) AS name
FROM posts p;

     name     
--------------
 былое и думы
 технологии
 былое и думы
 семья
(4 rows)


=> SELECT DISTINCT unnest(p.tags) AS name
FROM posts p;

     name     
--------------
 семья
 былое и думы
 технологии
(3 rows)

Тут хорошо видно, что имеет место дублирование данных.

Как видно, оба подхода вполне могут применяться. В простых случаях массивы выглядят проще и работают хорошо. В более сложных сценариях (представьте, что вместе с именем тега мы хотим хранить дату его создания; или требуется проверка ограничений целостности) классический вариант становится более привлекательным.

Работа с массивами в PL/pgSQL

Приведем некоторые примеры работы с массивами. Объявление переменной и инициализация массива целиком: 

=> DO $$
DECLARE
    a integer[];
BEGIN
    a := ARRAY[10,20,30];
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;

NOTICE:  {10,20,30}
DO

Одномерный массив можно заполнять и поэлементно - он автоматически расширяется. Если пропустить какие-то элементы, они получают неопределенные значения. 

=> DO $$
DECLARE
    a integer[3]; -- размер игнорируется
BEGIN
    a[2] := 10;
    a[3] := 20;
    a[6] := 30;
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;

NOTICE:  [2:6]={10,20,NULL,NULL,30}
DO

Итерация индексов элементов массива. Второй параметр array_lower и array_upper - номер размерности (единица для одномерных массивов). 

=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP 
        RAISE NOTICE 'a[%] = %', i, a[i];
    END LOOP;
END;
$$ LANGUAGE plpgsql;

NOTICE:  a[1] = 10
NOTICE:  a[2] = 20
NOTICE:  a[3] = 30
DO

Итерация самих элементов массива. 

=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP 
        RAISE NOTICE '%', x;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

NOTICE:  10
NOTICE:  20
NOTICE:  30
DO

Двумерные массивы (здесь мы использовали инициализацию с помощью другой конструкции). Но после инициализации расширить массив уже нельзя. 

=> DO $$
DECLARE
    a integer[][] := '{{10,20,30},{100,200,300}}';
BEGIN
    RAISE NOTICE '%', a;
    a[4][4] := 1;
END;
$$ LANGUAGE plpgsql;

NOTICE:  {{10,20,30},{100,200,300}}
ERROR:  array subscript out of range
CONTEXT:  PL/pgSQL function inline_code_block line 6 at assignment

Итерация индексов в двумерном массиве. Двойные квадратные скобки в объявлении переменной не обязательны, на самом деле. 

=> DO $$
DECLARE
    a integer[] := ARRAY[ARRAY[10,20,30],ARRAY[100,200,300]];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP -- по строкам
        FOR j IN array_lower(a,2)..array_upper(a,2) LOOP -- по столбцам
            RAISE NOTICE 'a[%][%] = %', i, j, a[i][j];
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

NOTICE:  a[1][1] = 10
NOTICE:  a[1][2] = 20
NOTICE:  a[1][3] = 30
NOTICE:  a[2][1] = 100
NOTICE:  a[2][2] = 200
NOTICE:  a[2][3] = 300
DO

Итерация элементов двумерного массива. 

=> DO $$
DECLARE
    a integer[] := ARRAY[ARRAY[10,20,30],ARRAY[100,200,300]];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP 
        RAISE NOTICE '%', x;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

NOTICE:  10
NOTICE:  20
NOTICE:  30
NOTICE:  100
NOTICE:  200
NOTICE:  300
DO

Итерация срезов массивов и обращение к отдельному срезу. 

=> DO $$
DECLARE
    a integer[] := ARRAY[ARRAY[10,20,30],ARRAY[100,200,300]];
    x integer[];
BEGIN
    FOREACH x SLICE 1 IN ARRAY a LOOP -- по строкам
        RAISE NOTICE 'SLICE 1: %', x;
    END LOOP;
    FOREACH x SLICE 2 IN ARRAY a LOOP -- весь массив
        RAISE NOTICE 'SLICE 2: %', x;
    END LOOP;
    RAISE NOTICE '[1:2][2:3] = %', a[1:2][2:3];
END;
$$ LANGUAGE plpgsql;

NOTICE:  SLICE 1: {10,20,30}
NOTICE:  SLICE 1: {100,200,300}
NOTICE:  SLICE 2: {{10,20,30},{100,200,300}}
NOTICE:  [1:2][2:3] = {{20,30},{200,300}}
DO

Функции с переменным числом параметров и полиморфные функции

В теме "SQL. Функции" мы создавали функцию maximum, которая находила максимальное из трех чисел. Обобщим ее на произвольное число аргументов. Для этого объявим один VARIADIC-параметр: 

=> CREATE FUNCTION maximum(VARIADIC a integer[]) RETURNS integer AS $$
DECLARE
    x integer;
    maxsofar integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
    RETURN maxsofar;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE FUNCTION

Пробуем: 

=> SELECT maximum(12, 65, 47);

 maximum 
---------
      65
(1 row)


=> SELECT maximum(12, 65, 47, null, 87, 24);

 maximum 
---------
      87
(1 row)


=> SELECT maximum(null, null);

 maximum 
---------
        
(1 row)

Для полноты картины эта функция может быть сделана полиморфной, чтобы принимать любой тип данных (для которого, конечно, должны быть определены операции сравнения). 

=> DROP FUNCTION maximum(integer[]);

DROP FUNCTION

=> CREATE FUNCTION maximum(VARIADIC a anyarray, maxsofar OUT anyelement) AS $$
DECLARE
    x maxsofar%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE FUNCTION

Проверим: 

=> SELECT maximum(12, 65, 47);

 maximum 
---------
      65
(1 row)


=> SELECT maximum(12.1, 65.3, 47.6);

 maximum 
---------
    65.3
(1 row)

Вот теперь у нас получился практически полный аналог функции greatest!

Конец демонстрации.