Как написать триггер для данной тестовой базы данных?

Question

[Василий Теркин]

Опишу ситуацию, чтобы мой вопрос был максимально понятен: существует программа, которая отрабатывает "сырые" данные и вносит их в базу данных postgresql. Посредством той же программы пользователь вводит свои sql-запросы и анализирует данные из базы. Но вот беда: чтобы проанализировать некоторые частные случаи из базы данных, нужно создавать много сложных запросов - что приводит к значительному снижению скорости анализа. Отсюда вопрос: как создать, если это возможно, таблицу в которая будет заполняться автоматически (средствами postgres) на основе данных ячеек других таблиц.
Схематически мы имеем следующее:

А вот, что хотелось бы иметь:


У меня довольно поверхностное знание postgres, поэтому и прошу помощи с данным вопросом: что конкретно почитать, какие способы существуют для решения поставленной задачи и т.д.

Заранее спасибо всем, кто хотя бы прочитал этот вопрос =)

UPD Нужно уточнить следующее: SQL-запросы в упомянутой программе задаются строго собственным синтаксисом программы (т.е. это не чистый sql), а этот синтаксис поддерживает лишь ограниченное количество функций. Поэтому задача стоит такая: через pgadmin добавить некую команду, которая создаст новую таблицу, которая будет добавлять данные на основе встроенной логики. Возможно ли это?

ДОПОЛНЕНИЕ: Почитал про SQL и базы данных и хотелось бы для себя прояснить некоторые моменты, поэтому прошу тех помощи у тех, кто разбирается в написании триггеров для postgresql. Помогите, пожалуйста, написать триггер для следующей тестовой базы данных:

Смысл в том, чтобы создать еще одну таблицу:

Где action2_items_table.user_id = user_table.user_id,
а action2_items_table.item_names - текстовое поле, в котором через запятую перечисляются все уникальные значения user_actions_table.item_id для юзера при условии, что user_actions_table.action2 = true.

Answer 1

[vyegorov]

Добро пожаловать в мир программирования на стороне СУБД! ;)

Триггера вам в помощь. Ссылка на PL/pgSQL, он ставиться по умолчанию.

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

Если используется 9.3 и выше, то можно воспользоваться Материализованными представлениями, хотя они могут и не подойти к вашим условиям. Однако до выхода 9.4 есть ограничение: представление надо обновить если изменились исходные данные. Во время обновления представление недоступно.

18/Авг-2014: Позволю себе несколько замечаний по схеме:

1. использовать `_table` в названии таблицы не надо. Это как “масло масляное”;
2. некоторые таблицы используют единственное число в названии, некоторые — множественное. Это неудобно, т.к. заставляет помнить названия конкретных таблиц, или, при разрастании схемы, приводит к частому просмотру определения таблиц. Я предпочитаю называть таблицы в единственном числе, т.к. логическая единица из таблицы — запись — представляет собой одного user-а, customer-а и т.п.;
3. тип данных колонки `item_id` не совпадает, это не хорошо и может привести к неожиданным последствиям;
4. Я предпочитаю везде использовать тип `text` для символьных значений, т.к. внутри все строковые типы обрабатываются одинаково. (Это из области предпочтений.)

Если я правильно понял задачу, то она решается таким запросом:

SELECT ua.item_id, string_agg(i.item_name,',')
  FROM user_actions_table ua
  JOIN item_table USING (item_id)
 WHERE ua.action2
 GROUP BY ua.item_id;

Самое простое решение — построить материализованное представление вот так:

CREATE MATERIALIZED VIEW action2_items_table AS
SELECT ua.item_id, string_agg(i.item_name,',')
  FROM user_actions_table ua
  JOIN item_table USING (item_id)
 WHERE ua.action2
 GROUP BY ua.item_id
 ORDER BY ua.item_id;

О концепции лучше читать в доках, коротко — будет сделана таблица с со структурой и данными возвращаемыми запросом, при этом сам запрос будет также сохранен. Данные будут “заморожены”, т.е. любые изменения в исходных таблицах видны не будут. Обновить данные можно командой:REFRESH MATERIALIZED VIEW action2_items_table;
В текущей версии PostgreSQL (9.3 пока еще, вот-вот выйдет 9.4) эта команда приведет к блокировкам: `action2_items_table` будет недоступна до окончания “обновления”. Попробуйте, если данная операция займет не более 1 секунды, я думаю, что это допустимо.

В 9.4-же обновляться можно параллельно с запросами:

REFRESH MATERIALIZED VIEW CONCURRENTLY  action2_items_table;

Правда, для этого необходим уникальный индекс:

CREATE UNIQUE INDEX u_action2_items_table ON action2_items_table(item_id);

Кстати, такой индекс не помешает и в текущей (9.3) версии.

Написать этот функционал процедурно (PL/pgSQL) сложно, но можно. Напишите, если вам такое решение не подходит.

Comments

[Василий Теркин]

Спасибо за ваш ответ. Пойду-ка я почитаю =)

[Василий Теркин]

vyegorov, если у вас есть время и желание, помогите, пожалуйста, с вопросом в новом ДОПОЛНЕНИИ.

Answer 2

[Swartalf]

А view не подойдет?

www.postgresql.org/docs/9.2/static/sql-createview.html

Comments

[Василий Теркин]

Спасибо за ответ, благодаря ему могу уточнить свой вопрос: см. UPD в вопросе.