Какая инструкция изменяет существующие данные в таблице или представлении

SQL UPDATE — это оператор, который используется для изменения записей в таблице. Он позволяет обновлять значения в столбцах таблицы в соответствии с определенным условием.

Синтаксис SQL UPDATE выглядит так:

			UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
		

UPDATE table_name указывает таблицу, в которой нужно обновить данные.

SET column1 = value1, column2 = value2, ... определяет столбцы, которые нужно обновить, и новые значения для них.

WHERE condition определяет условие, по которому будут выбраны записи для обновления. Если это условие не указано, то будут обновлены все записи в таблице.

Примеры использования Update SQL

Обновление одной записи

Чтобы обновить только одну запись в SQL, необходимо использовать оператор UPDATE с условием, которое точно соответствует только одной записи.

			UPDATE students
SET grade = '3'
WHERE student_id = 12345;
		

В этом примере оператор UPDATE обновит столбец grade для студента с идентификатором 12345 и установит оценку “3” только для этой записи.

Чтобы обновить только одну запись, условие WHERE должно быть уникальным и соответствовать только этой записи в таблице.

Обновление записей

Чтобы обновить несколько записей в SQL с помощью UPDATE, используйте оператор WHERE для указания условий, под которые попадают сразу несколько строк или столбцов.

В примере ниже оператор UPDATE обновит все записи в таблице users и установит значение active для всех записей в поле status , если значение age больше 25 лет:

			UPDATE users
SET status = 'active'
WHERE age > 25;
		

Важно! Если обновить записи в SQL при помощи UPDATE и не указать условие WHERE, то все записи в таблице будут обновлены.

Пример использования команды UPDATE без указания WHERE:

			UPDATE users
SET status = 'inactive';
		

В этом примере все записи в таблице users будут обновлены, и значения поля status будут установлены как inactive для всех записей без ограничений. Использование такого синтаксиса допустимо только в тех случаях, когда требуется массовое обновление всех данных.

Обновление нескольких столбцов

C помощью оператора SQL UPDATE можно обновлять сразу несколько столбцов за один запрос:

			UPDATE customers
SET address = '123 New St', city = 'New York'
WHERE customer_id = 345;

		

Обновление с функцией LIMIT

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

			UPDATE customers
SET status = 'active'
WHERE city = 'Moscow'
LIMIT 5;

		

Обновление нескольких таблиц

С помощью оператора UPDATE можно обновить сразу несколько таблиц за один запрос:

			UPDATE orders, customers
SET orders.status = 'processed', customers.last_order_date = NOW()
WHERE orders.customer_id = customers.customer_id AND orders.order_id = 5001;

		

В этом запросе обновляются данные как в таблице заказов, так и в табличке клиентов для конкретного заказа.

Обновление с DEFAULT

Функция DEFAULT возвращает значение поля к его значению по умолчанию.

			UPDATE products
SET discount = DEFAULT
WHERE product_id = 1001;

		

Здесь скидка для продукта с идентификатором 1001 будет возвращена к значению по умолчанию, которое было определено при создании таблицы.

Обновление с INNER JOIN

Функция INNER JOIN используется для обновления данных на основе совпадений между таблицами:

			UPDATE employees
INNER JOIN departments ON employees.department_id = departments.department_id
SET employees.salary = 60000
WHERE departments.name = 'HR';

		

Здесь зарплата всех сотрудников отдела кадров будет установлена на уровне 60000, используя условие соединения между таблицами employees и departments.

Обновление с LEFT JOIN

При необходимости обновления данных даже в тех строках, где нет соответствий между таблицами, используем LEFT JOIN.

			UPDATE employees
LEFT JOIN bonuses ON employees.employee_id = bonuses.employee_id
SET employees.bonus = bonuses.amount
WHERE bonuses.year = 2024;

		

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

Заключение

SQL UPDATE используется для обновления или модификации существующих данных в таблице, что позволяет поддерживать актуальность информации в базе данных.

UPDATE определяет таблицу для обновления данных, SET определяет столбцы для обновления, а WHERE определяет условия.

Важно не забывать определить условие WHERE, чтобы случайно не обновить все данные в столбцах.

Для редактирования существующих записей в таблицах существует SQL оператор UPDATE.

Общая структура запроса с оператором UPDATE

UPDATE имя_таблицы
SET поле_таблицы1 = значение_поля_таблицы1,
    поле_таблицыN = значение_поля_таблицыN
[WHERE условие_выборки]


Так, например, если нужно изменить имя, то запрос будет иметь следующий вид:
UPDATE FamilyMembers
SET member_name = "Andie Anthony"
WHERE member_name = "Andie Quincey";



Будьте внимательны, когда обновляете данные. Если вы пропустите оператор WHERE, то будут обновлены все записи в таблице.

Вычисляемые значения
В запросах на обновление данных можно менять значения, опираясь на предыдущее значение.
UPDATE Payments
SET unit_price = unit_price * 2;


Разрешается также значения одних столбцов присваивать другим столбцам. Но при этом, естественно, типы столбцов должны быть совместимыми.

Обновление данных в базах — один из ключевых элементов при работе в MySQL. Под обновлением подразумевается изменение значений в существующих записях таблицы. Обновление допускает различные варианты: как изменение значений полей в группе строк (в том числе и всех строк таблицы), так и корректировку значения поля отдельной строки. 

Для полноценной работы как с локальными базами данных, так и с облачными базами данных timeweb.cloud, важно понимать синтаксис команд для обновления данных.

Картинка с сайта: timeweb.cloud

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

В случае обновления одной таблицы синтаксис выглядит следующим образом:

UPDATE LOW_PRIORITY IGNORE table_reference
    SET assignment_list
    WHERE where_condition
    ORDER BY ...
    LIMIT row_count

Обязательный параметр:

SET assignment_list — указывает, какие столбцы нужно изменить и каким образом (assignment_list — список столбцов и новых значений).

Необязательные параметры:

• LOW_PRIORITY — если указан этот параметр, выполнение UPDATE откладывается до тех пор, пока другой пользователь не решит просмотреть данные таблицы.
• IGNORE — в этом сценарии выполнение UPDATE не прерывается, даже если в процессе возникают какие-либо ошибки. Если возникают дубликаты в столбце с уникальным ключом, то такие строки не обновляются. 
• WHERE where_condition — широко известный оператор задает условия, по которым отбираются строки для обновления (where_condition — список условий). Если параметры WHERE не указаны, обновятся все строки таблицы.
• ORDER BY — если указан этот параметр, строки обновляются в заданном порядке.
• LIMIT row_count — ограничение на количество обновляемых строк (row_count — некое число строк). Считаются строки, которые попадают под условие WHERE, независимо от того, изменились они или нет. 

В случае обновления нескольких таблиц одновременно синтаксис будет следующим:

UPDATE LOW_PRIORITY IGNORE table_references
    SET assignment_list
    WHERE where_condition

table_references — список таблиц, которые мы хотим изменить. Они изменятся таким образом, как указано в assignment_list. 

При обновлении нескольких таблиц нельзя использовать параметр ORDER BY или LIMIT. Помимо этого, при обновлении нескольких таблиц нет гарантий, что они будут обновлены в указанном порядке.

Остальные параметры запроса (необязательные) — LOW_PRIORITY, IGNORE и WHERE — работают так же, как в сценарии обновления одной таблицы.

Итак, давайте рассмотрим примеры использования оператора UPDATE в MySQL.

Для начала создадим базу данных для книжного магазина. В ней будет 4 таблицы: книги, жанры, писатели и покупки. 

Воспользуемся операторами CREATE TABLE и INSERT INTO, чтобы создать таблицы author, genre, book и sales. 

CREATE TABLE author (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name_author VARCHAR(50) NOT NULL
    );
INSERT INTO author (name_author) 
VALUES ('Leo Tolstoy'), 
('Franz Kafka'), 
('Nikolai Gogol'),
('William Shakespeare'),
('Homer');
CREATE TABLE genre (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name_genre VARCHAR(30) NOT NULL
);
INSERT INTO genre (name_genre) 
VALUES ('Realist novel'),
('Dystopian novel'),
('Picaresque novel'),
('Epic poetry');
CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50), 
    author_id INT NOT NULL, 
    genre_id INT,
    price DECIMAL(8,2) NOT NULL, 
    amount INT DEFAULT 0, 
    FOREIGN KEY (author_id) REFERENCES author (id),
    FOREIGN KEY (genre_id) REFERENCES genre (id)
);
INSERT INTO book (title, author_id, genre_id, price, amount) 
VALUES ('Anna Karenina', 1,1, 650.00, 15),
('The Castle', 2,2, 570.20, 6),
('Dead Souls', 3,3, 480, 2),
('Iliad', 5,4, 518.99, 4),
('Odyssey', 5,4, 518.99, 7);
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    count INT NOT NULL,
    cost DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (book_id) REFERENCES book (book_id)
    );

 Получаем следующие таблицы. Таблица book:

Картинка с сайта: timeweb.cloud

Столбцы:

• book_id – уникальный идентификатор книги;
• title – название книги;
• author_id – идентификатор автора (внешний ключ);
• genre_id – идентификатор жанра (внешний ключ);
• price – цена книги за один экземпляр; 
• amount – количество книг на складе.

Таблица genres:

Столбцы: 

• id – уникальный идентификатор;
• name_genre – обозначение жанра.

Таблица author:

Столбцы:

• id – уникальный идентификатор;
• name_author – имя автора книги.

Таблица sales:

Картинка с сайта: timeweb.cloud

Столбцы:

• id – уникальный идентификатор операции;
• book_id – уникальный идентификатор книги из таблицы book (внешний ключ);
• count – количество купленных книг;
• cost – общая стоимость товаров.

Операции по обновлению данных

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

1. Обновление всех строк

Если при использовании UPDATE вы не используете задающий условия параметр WHERE, то будут обновлены все строки в таблице. Предположим, в книжном магазине проходит акция «Всё по 500» — изменим цену всех книг на фиксированную 500 рублей:

UPDATE book 
SET price=500;

В результате выполнения запроса мы получим такую табличку:

Картинка с сайта: timeweb.cloud

Если мы попробуем присвоить значение, которое уже находится в столбце, то MySQL заметит это и не обновит его.

Если мы захотим присвоить значение NULL столбцу, при создании которого было указано NOT NULL, то запрос вернет ошибку: 

Column 'name_column' cannot be null

В то же время, если указать параметр IGNORE, то значение будет изменено на значение по умолчанию для конкретного типа: 0 для числовых, “” для символьных и «нулевое» для дат. Например, 0000 для типа данных YEAR или 0000-00-00 00:00:00 для типа DATETIME.

дбаас

2. Обновление строк с условием

Изменение всех строк таблицы требуется довольно редко. Гораздо чаще нам необходимо обновить значения для какой-то конкретной записи или для нескольких. Допустим, мы хотим изменить строки по какому-то условию. Например, устроим распродажу книг, которых осталось в наличии меньше 5 штук. Для этого в MySQL с командой UPDATE используем оператор WHERE:

UPDATE book 
SET price=300
WHERE amount < 5;

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Хорошо видно, что изменились только строки с книгами Dead Souls и Iliad, так как только они удовлетворяли прописанному в запросе условию.

3. Обновление значений с выражением

При обновлении мы можем задавать столбцу не только статичное значение, но и выражения. Предположим, в магазине проходит акция, и на книги русских писателей объявлена скидка в 15%:

UPDATE book 
SET price=price * 0.85
WHERE author_id in (1,3);

В таблице author имеется только два русских писателя – Leo Tolstoy и Nikolai Gogol с author_id 1 и 3 соответственно.

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Обновление значений происходит в определенном порядке: слева направо. Например, следующий запрос сначала увеличит значение amount на 1, а потом удвоит его:

UPDATE book 
SET amount = amount + 1, amount =amount*2;

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

4. Обновление с DEFAULT

Также мы можем изменить значение строк на значения «по умолчанию» DEFAULT, которые задаются при создании или изменении таблицы. Для того чтобы узнать, какие значения в нашей таблице используются по умолчанию, выполним запрос:

DESC book

В результате получим следующую структуру нашей таблицы в MySQL:

Картинка с сайта: timeweb.cloud

Заменим значения столбца amount на значение DEFAULT. Так как по умолчанию значение для amount было 0, мы должны получить все 0:

UPDATE book 
SET amount=DEFAULT;

Результат выполнения запроса соответствует ожиданиям:

Картинка с сайта: timeweb.cloud

5. Обновление нескольких столбцов

Используя один запрос, мы можем обновить сразу несколько столбцов. Например, изменим значения цены и количества у строк со значением book_id < 4:

UPDATE book 
SET price=price*0.9,
       amount = amount - 1
WHERE book_id < 4;

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

6. Использование LIMIT

Используя параметр LIMIT, мы можем ограничить количество записей, которые должны быть обновлены. Например, обновим только первую строку:

UPDATE book 
SET price=100
WHERE genre_id = 4
LIMIT 1;

В таблице имеется две строки с genre_id равным 4, но, так как мы указали LIMIT 1, обновится только одна:

Картинка с сайта: timeweb.cloud

Также следует отметить, что LIMIT N вовсе не означает, что мы обновим N строк. В результате запроса произойдёт обработка первых N строк, подходящих под условие WHERE, независимо от того, обновились эти строки в итоге или нет.

7. Обновление нескольких таблиц

В MySQL мы можем обновить сразу несколько таблиц:

UPDATE book, author 
SET amount=amount + 3, 
author.name_author = '-'
WHERE book.book_id = 4 and author.id = 4;

Результат запроса в таблице book:

Картинка с сайта: timeweb.cloud

Результат запроса в таблице authors:

8. Обновление таблиц с объединением (INNER JOIN)

Во время обновления можно также объединять таблицы при помощи команды INNER JOIN.

UPDATE book b INNER JOIN author a 
ON b.author_id = a.id
SET b.title = CONCAT(b.title, ' (', a.name_author,')');

Картинка с сайта: timeweb.cloud

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

UPDATE book, author a 
SET b.title = CONCAT(b.title, ' (', a.name_author,')')
WHERE b.author_id = a.id;

9. Обновление таблиц с объединением (LEFT JOIN)

Также мы можем использовать LEFT JOIN. В этом случае обязательно указывать, что мы используем именно левое объединение. Например, можно изменить на складе количество книг после их покупки. Добавим в таблицу sales две строки:

INSERT INTO sales (book_id, count, cost) 
VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3), 
(3, 1, (SELECT price FROM book WHERE book_id = 3)*1);

Магазин продал 3 книги Anna Karenina и 1 книгу Dead Souls. Выполним запрос: 

UPDATE book LEFT JOIN sales on book.book_id = sales.book_id
SET amount = amount - count
WHERE sales.book_id is not NULL;

В итоге обновления видим, что количество книг на складе уменьшилось (для тех, которые мы продали):

Картинка с сайта: timeweb.cloud

Если мы попробуем не использовать LEFT JOIN, то получим ошибку «Out of range value for column ‘amount’ at row 3», т.к. amount не может быть отрицательным. Или, если добавить IGNORE, получим:

Картинка с сайта: timeweb.cloud

Как можно видеть в данном случае во всех строках количество уменьшилось на три книги, что нам не очень подходит.

10. Обновление с CASE, IF, IFNULL, COALESCE

При обновлении таблицы также возможно использовать условные операторы, такие как CASE, IF и т.д.

Функция CASE проверяет истинность набора условий и, в зависимости от результата, возвращает один из возможных результатов. Синтаксис при работе с UPDATE в MySQL для операторов CASE и WHEN будет следующий:

UPDATE book
SET price = 
  CASE genre_id
  WHEN 1 THEN 100
  WHEN 2 THEN 150
  ELSE price 
  END;

В данном случае, если книга имеет жанр 1 мы устанавливаем стоимость 100, если жанр 2 – стоимость 150.

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Если книга имеет жанр 4, то мы уменьшаем ее стоимость на 200, иначе оставляем стоимость прежней:

UPDATE book
SET price = IF (genre_id = 4, price-200, price);

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Функция IFNULL проверяет значение выражения – если оно имеет значение NULL, то возвращается определенное значение, в противном случае возвращается само выражение. Пусть одно из значений amount оказалось NULL:

Картинка с сайта: timeweb.cloud

Проверим все значения в столбце amount, и если встретится NULL, заменим его на 0: 

UPDATE book
SET amount = IFNULL(amount, 0);

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Функция COALESCE довольна похожа на IFNULL. Основная особенность заключается в том, что данная функция может принимать сразу несколько значений (два и более). Как и IFNULL, возвращает первое не равное NULL.

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

Картинка с сайта: timeweb.cloud

И выполним запрос:

UPDATE test_table
SET col4 = COALESCE(col1, col2, col3, 'нет значения');

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

11. Обновление с сортировкой

Сортировка может помочь при обновлении поля с уникальным ключом. Если мы захотим сдвинуть наши id на 1, то, изменив первую строку, мы получим две строки, у которых id = 2, и возникнет ошибка. Но если добавить ORDER BY и начать обновлять с конца, то запрос успешно выполнится:

UPDATE book
SET book_id=book_id+1 

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

12. Обновление на основе данных из других таблиц

Также в MySQL при работе с UPDATE в условии WHERE возможно использовать вложенные команды SELECT и FROM. В рассматриваемом примере мы сначала получаем идентификатор жанра ‘Epic poetry’, а потом на основе полученного значения отбираем строки для обновления таблицы. 

UPDATE book
SET amount = 0
WHERE genre_id = (
   SELECT id
   FROM genre 
   Where name_genre = 'Epic poetry'
);

Картинка с сайта: timeweb.cloud

Как вариант, мы можем выбрать значения, которые нужно изменить, используя запрос: 

UPDATE book
SET price = (
SELECT MIN (cost)
FROM sales)
WHERE amount < 5;

Мы изменяем значения price всех книг, у которых количество на складе меньше 5, на минимальную сумму продажи. 

Картинка с сайта: timeweb.cloud

Минимальная сумма продаж у нас 480:

Картинка с сайта: timeweb.cloud

В этой ситуации невозможно обновить таблицу, выбрав значения из той же таблицы в подзапросе. Но есть вариант использовать небольшую хитрость – мы можем объединить таблицу с собой:

UPDATE book AS book_1
    INNER JOIN(
        SELECT genre_id, MIN(amount) AS min_amount
        FROM book
        GROUP BY genre_id
    ) AS book_2 ON book_1.genre_id = book_2.genre_id
SET book_1.amount = book_2.min_amount;

В данном случае подзапрос создает временную таблицу для присоединения и закрывает ее до того, как начнется выполнение UPDATE.

Подзапрос находит минимальное количество книг для каждого жанра, после чего используется для обновления столбца amount. В нашей таблице только у жанра 4 имеется больше одной строки. Значения в обеих строках должно замениться на минимальное для этого жанра – 4.

Результат выполнения запроса:

Картинка с сайта: timeweb.cloud

Есть еще один вариант обновления – использование SELECT FROM SELECT:

UPDATE book AS book_1
SET book_1.price = (SELECT MIN(price) AS min_price FROM (
SELECT price 
FROM book) as book_2);

В данном случае также создается временная таблица. Однако в этом случае присваивается только одно значение всем строкам.

Заключение

Мы постарались максимально подробно раскрыть особенности применения оператора UPDATE в MySQL. Но, конечно, практическое применение может продемонстрировать немало других интересных вариантов.

Данный оператор изменяет уже существующие данные в таблице.

Общий синтаксис выглядит следующим образом:

UPDATE table_1 t
SET t.column_1 = val_1,
    t.columm_2 = val_2
WHERE <condition>

UPDATE table_1 t
SET t.column_1 = val_1,
    t.columm_2 = val_2
WHERE <condition>

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

update employees emp
set emp.age = emp.age + 1

update employees emp
set emp.age = emp.age + 1

Можно добавлять любые условия в where, как и в select-запросах, чтобы обновить не все строки в таблице, а только те, которые удовлетворяют определенным условиям:

-- Увеличить возраст сотруднику с именем Антон Иванов
update employees emp
set emp.age = emp.age + 1
where emp.name = 'Антон Иванов';

-- Увеличить возраст сотруднику с именем Антон Иванов
update employees emp
set emp.age = emp.age + 1
where emp.name = 'Антон Иванов';

-- Увеличить возраст сотруднику с id = 10 и привести имя к верхнему регистру
update employees emp
set emp.age = emp.age + 1,
    emp.name = upper(emp.name)
where emp.id = 10;

-- Увеличить возраст сотруднику с id = 10 и привести имя к верхнему регистру
update employees emp
set emp.age = emp.age + 1,
    emp.name = upper(emp.name)
where emp.id = 10;

При обновлении мы можем использовать подзапросы для получения новых значений:

update employees emp
-- для каждого сотрудника получаем возраст из таблицы страховой карточки
-- и присваиваем это значение в колонку age таблицы employees
set emp.age = (select age from insurance_card ic where ic.emp_id = emp.id)
where emp.age is null

update employees emp
-- для каждого сотрудника получаем возраст из таблицы страховой карточки
-- и присваиваем это значение в колонку age таблицы employees
set emp.age = (select age from insurance_card ic where ic.emp_id = emp.id)
where emp.age is null

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

С использованием подзапросов можно обновлять сразу несколько колонок в таблице:

update employees emp
set(
    emp.age,
    emp.passport_no
) = (select ic.age, ic.passport_no from insurance_card ic
     where ic.emp_id = emp.id)

update employees emp
set(
    emp.age,
    emp.passport_no
) = (select ic.age, ic.passport_no from insurance_card ic
     where ic.emp_id = emp.id)

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

-- Получим ошибку!
update employees emp
set(
    emp.age,
    emp.passport_no
) = (20, '324589')

-- Получим ошибку!
update employees emp
set(
    emp.age,
    emp.passport_no
) = (20, '324589')

В результате получим ошибку ORA-01767: UPDATE ... SET expression must be a subquery.

Но зато можно вот так:

update employees emp
set(
    emp.age,
    emp.passport_no
) = (select 20, '324589' from dual)

update employees emp
set(
    emp.age,
    emp.passport_no
) = (select 20, '324589' from dual)

Картинка с сайта: habr.com

Представления (Views) — это не совсем то, о чём вы можете подумать. В этой статье мы разберём правила перезаписи PostgreSQL, как они работают, что они могут и чего не могут делать.

Начиная с версии 10, PostgreSQL поддерживает концепцию правил перезаписи, которые позволяют изменять способ выполнения запросов.

Фактически правила перезаписи — это то, как представления реализованы в PostgreSQL. Когда вы обращаетесь к представлению, ваш запрос фактически переписывается в соответствии с правилом, которое было создано при создании представления. По сути, представление — это, как мы сейчас увидим, правило перезаписи.

Однако не стоит расслабляться — некоторые условности всё же есть. Например, эти правила перезаписи сильно отличаются от плагинов перезаписи MySQL и в целом являются более комплексными.

Основные положения

Когда вы определяете правило перезаписи, вы сообщаете PostgreSQL, как обрабатывать определённый тип запроса к определённой таблице или представлению.

Правила перезаписи могут быть определены для SELECT, UPDATE, INSERT и DELETE. Они не существуют для любых других запросов, таких как DDL, SET, FETCH и т. д., по причинам, которые быстро станут очевидными.

Правила SELECT

Правила перезаписи для SELECT сильно ограничены; по сути, они могут определять только представление. На самом деле правила перезаписи для SELECT редко создаются напрямую; с тем же успехом вы можете определить представление, поскольку оператор CREATE VIEW приведёт к тому же результату, а именно к созданию новой строки в таблице pg_catalog.pg_rewrite.

Для этих ограничений есть очень веская причина: PostgreSQL включает правила в свою интерпретацию запросов. В случае правил SELECT он заменит имя отношения на то, что указано в правиле. Это означает, что правило SELECT должно быть простым SELECT, и поэтому правило не может делать ничего такого, что не может делать обычное представление.

Рассмотрим пример. Возьмём простую таблицу:

CREATE TABLE Persons (
  id INT PRIMARY KEY,
  first_name VARCHAR(200) NOT NULL,
  last_name VARCHAR(200) NOT NULL,
  dob DATE
);

INSERT INTO Persons VALUES
(1, 'Amy', 'Adams', '1974/08/20'),
(2, 'Brigitte', 'Bardot', '1934/09/28');

Теперь предположим, что мы хотим получить имя и фамилию в объединённом виде. Мы можем создать представление:

CREATE VIEW Persons_v AS
SELECT id, CONCAT(first_name, ' ', last_name) AS name, dob
FROM Persons

Посмотрим, что у нас получилось:

Картинка с сайта: habr.com

Это, конечно, самый обычный способ создания представления. Но мы можем сделать то же самое, создав таблицу и правило SELECT. Таблица будет превращена в представление путём добавления правила:

CREATE TABLE Persons_t (
  id INT,
  name TEXT,
  dob DATE
);

CREATE RULE "_RETURN" AS
ON SELECT TO Persons_t DO INSTEAD
SELECT id, CONCAT(first_name, ' ', last_name) AS name, dob
FROM Persons;

Несколько моментов, на которые следует обратить внимание:

• В таблице Persons_t не определён первичный ключ. Если бы он был, мы не смогли бы создать для него правило SELECT.
• Столбец name в Persons_t имеет тип TEXT, потому что это тип, возвращаемый функцией CONCAT.
• Правило называется _RETURN, потому что все правила SELECT должны иметь такое имя.

С этой «таблицей» Persons_t и установленным правилом перезаписи у нас теперь есть представление, хотя мы никогда напрямую не вызывали CREATE VIEW. Оно отразится, например, в pg_catalog.pg_views.

Вы почти наверняка никогда не будете этого делать; практически нет причин делать это таким образом, кроме как для удовлетворения собственного любопытства. А вот другие виды правил перезаписи намного интереснее.

Правила UPDATE, INSERT, и DELETE

Мы уже узнали, что правила SELECT фактически ограничены тем, что могут делать представления.

Однако для всех остальных правил перезаписи мы получаем гораздо больше свободы, поскольку они предназначены для определения того, как должны обрабатываться обновления, часто (но не только) в представлениях.

Допустим, мы хотим добавить значения в наше представление Persons_v. Если мы попробуем очевидное:

INSERT INTO Persons_v (id, name, dob)
VALUES (3, 'Charlie Chaplin', '1889/04/16')

Тогда мы получим ошибку:

[Code: 0, SQL State: 0A000]  ERROR: cannot insert into column "name" of view "persons_v"
  Detail: View columns that are not columns of their base relation are not updatable.

В этом есть смысл. PostgreSQL никак не может понять, как обращаться со столбцом name, который выводится с помощью формулы.

Вот тут-то и приходит на помощь правило INSERT:

CREATE RULE PersonsInsert AS
ON INSERT TO Persons_v DO INSTEAD
INSERT INTO Persons (id, first_name, last_name, dob)
VALUES (NEW.id, SPLIT_PART(NEW.name, ' ', 1), SPLIT_PART(NEW.name, ' ', 2), NEW.dob)

Теперь мы можем добавить значения в представление Persons_v, и значения для столбцов first_name и last_name будут обработаны правильно:

INSERT INTO Persons_v (id, name, dob)
VALUES (3, 'Charlie Chaplin', '1889/04/16')

Вот что получилось:

Картинка с сайта: habr.com

Попробуем заморочиться

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

Давайте создадим ещё одну таблицу:

CREATE TABLE Addresses (
  id INT PRIMARY KEY,
  street varchar(200) NOT NULL,
  city varchar(100) NOT NULL,
  person_id int REFERENCES Persons ON DELETE CASCADE,
  end_date timestamp
);

INSERT INTO Addresses (id, street, city, person_id) VALUES
(100, '1428 Elm Street', 'Springwood', 1),
(101, '742 Evergreen Terrace', 'Springfield', 2),
(102, '221B Baker Street', 'London', 3);

Столбец end_date сыграет свою роль позже, поэтому пока не обращайте на него внимания.

Теперь мы создадим новое представление, охватывающее обе таблицы:

CREATE VIEW PersonAddress AS
SELECT p.id as pid,
        CONCAT(first_name, ' ', last_name) AS name,
        a.id as aid,
        CONCAT(street, ', ', city) as address
FROM Persons p
LEFT JOIN Addresses a
ON a.person_id = p.id
WHERE a.end_date IS NULL

Посмотрим, что у нас получилось:

Картинка с сайта: habr.com

Как и в прошлый раз, если мы попытаемся вставить представление PersonAddress, мы получим ошибку, потому что представление содержит два производных столбца.

Но мы можем создать правило, которое будет обрабатывать это правильно:

CREATE RULE PersonAddressInsert AS
ON INSERT TO PersonAddress DO INSTEAD
(
  INSERT INTO Persons (id, first_name, last_name)
    VALUES (NEW.pid, SPLIT_PART(NEW.name, ' ', 1), SPLIT_PART(NEW.name, ' ', 2));
  INSERT INTO Addresses (id, street, city, person_id)
    VALUES (NEW.aid, SPLIT_PART(NEW.address, ', ', 1), SPLIT_PART(NEW.address, ', ', 2), NEW.pid)
)

Затем мы можем вставить данные в обе таблицы одновременно с помощью представления:

INSERT INTO PersonAddress (pid, name, aid, address)
VALUES (4, 'Doris Day', 103, '42 Wallaby Way, Sydney')

Теперь у нас есть такое:

Картинка с сайта: habr.com

Двигаемся в сторону триггеров

Мы уже видели, что правила INSERT, UPDATE и DELETE могут содержать несколько утверждений, а это означает, мы можем подойти к делу творчески.

Например, что если мы не хотим обновлять адрес, а вместо этого пометим старый адрес как устаревший с помощью столбца end_date и вставим новый адрес?

Такие вещи обычно обрабатываются триггером, но если мы просматриваем представление для обновления, мы можем обработать это в правиле UPDATE:

CREATE RULE PersonAddressUpdate AS
ON UPDATE TO PersonAddress DO INSTEAD
(
  UPDATE Persons
    SET first_name=SPLIT_PART(NEW.name, ' ', 1), last_name=SPLIT_PART(NEW.name, ' ', 2)
    WHERE id=NEW.pid;
  INSERT INTO Addresses (id, street, city, person_id)
    VALUES ((SELECT MAX(id) + 1 FROM Addresses), SPLIT_PART(NEW.address, ', ', 1),
      SPLIT_PART(NEW.address, ', ', 2), NEW.pid);
  UPDATE Addresses SET end_date = NOW()
    WHERE id=NEW.aid;
)

Пуристы SQL могут немного прищуриться, но это будет работать. Теперь, когда мы выполним обновление представления, мы получим новую строку в таблице Addresses, старая строка будет иметь значение в столбце end_date, а имя человека будет обновлено:

UPDATE PersonAddress
SET name = 'Doris Kappelhoff', address = '32 Spooner Street, Quahog'
WHERE aid=103

Картинка с сайта: habr.com

Добавляем в таблицы параметры поведения

Вы, возможно, заметили DO INSTEAD, когда мы определяли правило. Существует также DO ALSO, который позволяет нам добавить поведение к обычному выполнению запроса.

По большому счёту правила не могут быть рекурсивными. Вы не можете иметь правило INSERT для таблицы T, которое включает INSERT для таблицы T — это просто перейдёт в бесконечную рекурсию.

Но можно изменить (скажем) UPDATE в таблице на (скажем) DELETE. Правила существуют не только для представлений.

Например, мы можем захотеть иметь механизм «сбора мусора», с функционалом автоматического удаления любого человека, у которого больше нет адреса. Это можно сделать с помощью правила, определённого для таблицы Addresses:

CREATE RULE AddressDelete AS
ON DELETE TO Addresses DO ALSO
DELETE FROM Persons
WHERE id = OLD.person_id AND
  (SELECT COUNT(*) from Addresses WHERE person_id = OLD.person_id) = 1

Теперь, если мы удалим последний адрес человека, то автоматически удалим человека, которому принадлежал этот адрес (в данном случае Чарли Чаплина):

DELETE FROM Addresses WHERE id=102

Картинка с сайта: habr.com

Можно определить несколько правил одного типа для одной таблицы; в этом случае они будут выполняться в алфавитном порядке.

Параметры разрешений

Поскольку правила могут затрагивать таблицы, не упомянутые в исходном запросе, возникает вопрос: какие разрешения должна использовать база данных при выполнении этих правил?

Так, в нашем последнем примере мы удалили адрес, что предполагает, имеющиеся у нас для этого соответствующие привилегии в таблице Addresses.

Но, возможно, неизвестно для нас, это удаление также удалило человека. Что, если у нас нет прав DELETE на таблицу Persons?

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

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

Верный ли это путь для выполнения подобных задач?

Правила перезаписи полезны, даже незаменимы, когда вы хотите работать с представлениями, и вам нужно определить, что происходит с INSERT, UPDATE и DELETE. Надеюсь, что хотя бы из-за этой информации моё руководство было для вас полезным.

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

Но как далеко вы должны идти в направлении правил перезаписи? То, что вы можете что-то сделать, не означает, что вы должны это делать.

Это непростой вопрос. Триггеры могут иметь больше смысла, поскольку их цель часто более ясна, и у них больше возможностей (BEFORE/AFTER/INSTEAD OF, FOR EACH ROW и т. д.).

Но правила перезаписи иногда проще выразить, чем триггеры. В документации PostgreSQL есть обсуждение этой темы для тех, кто хочет в неё погрузиться.

Моя общая рекомендация — использовать правила перезаписи только для того, чтобы делать нетривиальные представления обновляемыми, и использовать триггеры для всех других целей. Правила перезаписи менее знакомы большинству людей, и они совершенно уникальны для PostgreSQL. Имейте в виду, следующий человек, который будет поддерживать то, что вы делаете, может быть не таким опытным, как вы.

Но, опять же, каждая ситуация уникальна и поэтому, в конце концов, вам придётся полагаться только на своё тщательное усмотрение.

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

— 15% на все тарифы VDS (кроме тарифа Прогрев) — HABRFIRSTVDS.