**1. Явные транзакции.**
Любую транзакцию можно заключить в блок BEGIN TRANSACTION. Чтобы убедиться в атомарности, отменим транзакцию командой ROLLBACK.
```
BEGIN TRANSACTION;
DELETE FROM events;
ROLLBACK;
SELECT * FROM events;
```
Все мероприятия остались на месте. Транзакции полезны, когда модифицируются две или более таблицы, которые должны быть со- гласованы между собой.
**2. Хранимые процедуры**
Хранимые процедуры могут предложить радикальный выигрыш в производительности в обмен на не менее радикальный архитектурный компромисс. FUNCTION
**3. Триггеры**
Триггер автоматически запускает хранимую процедуру, когда происходят определенные события, например вставка или обновление. Это позволяет базе данных гарантировать требуемое поведение в ответ на изменение данных.
```
CREATE OR REPLACE FUNCTION log_event() RETURNS trigger AS $$
DECLARE
BEGIN
INSERT INTO logs (event_id, old_title, old_starts, old_ends)
VALUES (OLD.event_id, OLD.title, OLD.starts, OLD.ends);
RAISE NOTICE ‘Someone just changed event #%’, OLD.event_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
```
CREATE TRIGGER log_events
AFTER UPDATE ON events
FOR EACH ROW EXECUTE PROCEDURE log_event();
```
**4. Правила**
Правило RULE – это описание способа изменения разобранного дерева запроса. Всякий раз как Postgres выполняет команду SQL, порождается дерево запроса (в общем случае его называют абстрактным синтаксическим деревом).
```
CREATE RULE update_holidays AS ON UPDATE TO holidays DO INSTEAD
UPDATE events
SET title = NEW.name,
starts = NEW.date,
colors = NEW.colors
WHERE title = OLD.name;
```
Имея такое правило, мы можем обновить представление holidays непосредственно.
```
UPDATE holidays SET colors = ‘{“red”,”green”}’ where name = ‘Christmas Day’;
```
**5. Создание сводных таблиц с помощью crosstab()**
Cводные таблицы (pivot table), позволяют «свести» сгруппированные данные относительно результата какой-то другой операции.
В PostgreSQL имеется функция extract(), которая выделяет компонент даты или временной метки; она пригодится для группировки.
```
SELECT extract(year from starts) as year,
extract(month from starts) as month, count(*)
FROM events
GROUP BY year, month;
```
```
SELECT * FROM crosstab(
‘SELECT extract(year from starts) as year,
extract(month from starts) as month, count(*)
FROM events
GROUP BY year, month’,
‘SELECT * FROM month_count’
) AS (
year int,
jan int, feb int, mar int, apr int, may int, jun int,
jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY YEAR;
```
**6. Полнотекстовый поиск и многомерные кубы**
Нечеткий поиск
Разрешить в системе полнотекстовый поиск – значит позволить вводить неточные данные. Следует быть готовым к вводу названия "Brid of Frankstein" вместо "Bridе of Frankenstein".
Расстояние Левенштейна – это мера похожести двух строк, оно определяется как количество шагов, необходимое для преобразования одной строки в другую. Шагом считается замена, добавление и удале- ние одного символа. В PostgreSQL функция levenshtein() входит в дополнительный пакет fuzzystrmatch.
Триграммой называется группа из трех последовательных символов в строке. Дополнительный модуль pg_trgm выделяет из строки все возможные триграммы.
Сначала мы создадим индекс по названиям фильмов типа Generalized Index Search Tree (GIST) (обобщенное индексное дерево поиска). Такой тип индексов включен в ядро PostgreSQL по умолчанию.
```
CREATE INDEX movies_title_trigram ON movies
USING gist (title gist_trgm_ops);
```
Теперь, даже если искомая строка содержит опечатки, мы все равно получим приемлемые результаты.
```
SELECT *
FROM movies
WHERE title % ‘Avatre’;
```
### Полнотекстовый поиск
#### TSVector и TSQuery
Тип данных tsvector представляет строку в виде массива (или вектора) лексем, которые сравниваются со строкой, указанной в запросе, а тип tsquery представляет запрос на некотором естественном языке, например, английском или французском. Языку соответствует словарь (о котором мы будем говорить чуть ниже).
```
SELECT title
FROM movies
WHERE to_tsvector(title) @@ to_tsquery(‘english’, ‘night & day’);
```
#### Индексирование лексем
Полнотекстовый поиск – могучая штука. Но если таблицы не ин- дексированы, то работать он будет медленно. Команда EXPLAIN поз- воляет узнать детали плана выполнения запроса.
```
EXPLAIN
SELECT *
FROM movies
WHERE title @@ ‘night & day’;
```
```
CREATE INDEX movies_title_searchable ON movies
USING gin(to_tsvector(‘english’, title));
```
#### Метафоны
Оператор NATURAL JOIN – это INNER JOIN, в котором соединение автоматически производится по столбцам с одинаковыми именами (например, movies.actor_ id=movies_actors.actor_id).
```
SELECT title
FROM movies NATURAL JOIN movies_actors NATURAL JOIN actors
WHERE metaphone(name, 6) = metaphone(‘Broos Wils’, 6);
```
Заглянув в онлайновую документацию, вы обнаружите, что в модуле fuzzystrmatch есть и другие функции: dmetaphone() (двойной метафон), dmetaphone_alt() (для альтернативного произношения имени) и soundex() (очень старый алгоритм, который был придуман в 1880-х годах для сравнения типичных американских фамилий при проведении переписей в США).
Одно из самых полезных свойств метафонов заключается в том, что они возвращают строковые значения, а, значит, мы можем использовать их в сочетании с другими методами поиска строк. Например, строку, возвращенную функцией metaphone() можно разложить на триграммы, а затем упорядочить результаты по наименьшему расстоянию Левенштейна. Иными словами, запросить у системы имена, которые произносятся похоже на Robin Williams, и расположить их в порядке убывания похожести.