Практическое применение RETURNING

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

К примеру, имеется таблица клиентов

CREATE TABLE clients(
    id serial PRIMARY KEY, -- строка эквивалентна id int DEFAULT nextval('clients_id_seq') PRIMARY KEY
    name varchar NOT NULL
);

Значение поля ID формируется СУБД автоматически, если оно не задано явно. Для нумерации клиентов используется последовательность clients_id_seq. Поэтому при создании нового клиента без явного указания его идентификатора новое значение ID будет сформировано запросом

SELECT nextval('clients_id_seq')

Таким образом запрос

INSERT INTO clients(name) VALUES('Denis');

реально трансформируется в запрос

INSERT INTO clients(id, name) VALUES(nextval('clients_id_seq'), 'Denis');

Если нужно получить автоматически сгенерированный ID, то у разработчиков есть два стандартных варианта:

1. Явно получить новый ID и вставить его в запрос

   _new_id := nextval('clients_id_seq');
   INSERT INTO clients(id, name) VALUES(_new_id, 'Denis');
   

2. Дать СУБД возможность сгенерировать новый ID самой и после этого получить сгенерированное значение идентификатора

   INSERT INTO clients(name) VALUES(nextval('Denis');
   _new_id := currval('clients_id_seq');
   

Оба варианта используют два запроса для одной бизнес-операции.

Но можно обойтись одним запросом, использовав RETURNING

db=# INSERT INTO clients(name) VALUES('Denis') RETURNING id;
  id
-------
 10001
(1 row)

В этом случае запрос создаст нового клиента и в качестве результата вернет его ID.

Возвращать измененные значение полей может и UPDATE. Классический пример с изменение баланса счета в банке - надо уменьшить баланс клиента на 100.

Запрос будет таким.

UPDATE accounts SET balance = balance - 100 WHERE id = _id;

Чтобы получить обновленный баланс, надо сделать второй запрос

SELECT balance FROM accounts WHERE id = _id;

Если эти два запроса делать в различных транзакция или без соответствующих блокировок, то это может привести к беде - баланс клиента не будет соответствовать действительности из-за возможных конкурентных параллельных изменений одной и той же записи. Для предотвращения подобных случаев удобно использовать RETURNING. Запрос будет таким

db=# UPDATE accounts SET balance = balance - 100 WHERE id = _id RETURNING balance;
  balance
-----------
   200.5
(1 row)

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

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

RETURNING может возвращать не только данные одной строки, но и все измененные/обработанные строки для UPDATE и DELETE.

Пример, когда RETURNING возвращает список удаленных записей.

db=# DELETE FROM customers RETURNING *;
 id   |  name
------+--------
 1950 | Hana
 2709 | Maritza
 9192 | Miya
 6437 | Jamil
...

Пользуйтесь с удовольствием!

Дополнительная информация

PostgreSQL Documentation: INSERT

PostgreSQL Documentation: UPDATE

PostgreSQL Documentation: DELETE

PostgreSQL Documentation: Sequence Manipulation Functions

comments powered by Disqus