Пошук уроків, статей та іншого контенту
Поверніть створені, оновлені або видалені рядки без окремого запиту SELECT.
RETURNINGУ PostgreSQL оператори INSERT, UPDATE і DELETE можуть повертати рядки, яких вони безпосередньо торкнулися. Для цього використовується секція RETURNING.
Це дає змогу:
отримати ідентифікатор щойно створеного рядка;
побачити значення, згенеровані за замовчуванням;
отримати оновлені дані;
перевірити, які рядки були видалені;
не виконувати окремий запит SELECT після зміни даних.
Загальний принцип:
оператор_зміни
RETURNING список_виразів;RETURNING працює подібно до списку вибірки в SELECT: можна вказати окремі стовпці, * або вирази.
INSERT ... RETURNINGПісля вставки часто потрібно отримати значення, яке створила база даних. Наприклад, це може бути ідентифікатор із типом GENERATED ALWAYS AS IDENTITY, поточний час або значення за замовчуванням.
CREATE TEMPORARY TABLE tasks (
id integer GENERATED ALWAYS AS IDENTITY,
title text NOT NULL,
status text NOT NULL DEFAULT 'new',
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO tasks (title)
VALUES ('Підготувати звіт')
RETURNING id, title, status, created_at;Результат міститиме вставлений рядок, зокрема значення id, status і created_at, які не вказувалися явно.
Можна повернути всі стовпці:
INSERT INTO tasks (title)
VALUES ('Перевірити пошту')
RETURNING *;Якщо INSERT додає кілька рядків, RETURNING поверне їх усі:
INSERT INTO tasks (title)
VALUES
('Оновити документацію'),
('Написати тести')
RETURNING id, title;Порядок рядків у результаті відповідає порядку, в якому PostgreSQL обробив вставку, але приклад не повинен покладатися на певний порядок без ORDER BY у зовнішньому запиті.
У RETURNING можна використовувати вирази:
INSERT INTO tasks (title)
VALUES ('Створити реліз')
RETURNING
id,
upper(title) AS title_upper,
status = 'new' AS is_new;У результаті будуть стовпці id, title_upper і is_new.
UPDATE ... RETURNINGUPDATE ... RETURNING повертає вже оновлені значення рядків:
UPDATE tasks
SET status = 'done'
WHERE title = 'Підготувати звіт'
RETURNING id, title, status;Якщо умова WHERE відповідає кільком рядкам, будуть повернуті всі оновлені рядки.
RETURNING також зручний, коли нове значення обчислюється під час оновлення:
UPDATE tasks
SET title = title || ' — завершено',
status = 'done'
WHERE status = 'new'
RETURNING id, title, status;Якщо жоден рядок не відповідає умові WHERE, оператор не завершується помилкою, але RETURNING не повертає жодного рядка.
Застосунок може використати кількість отриманих рядків, щоб зрозуміти, чи відбулося оновлення:
UPDATE tasks
SET status = 'in_progress'
WHERE id = 999999
RETURNING id;Порожній результат означає, що рядка з таким id не знайдено або він не відповідає умові.
DELETE ... RETURNINGDELETE ... RETURNING повертає рядки, які були видалені:
DELETE FROM tasks
WHERE status = 'done'
RETURNING id, title, status;Це може бути корисно, якщо після видалення потрібно:
показати користувачеві видалені дані;
записати їх у журнал;
отримати ідентифікатори видалених об’єктів;
перевірити, чи справді щось було видалено.
Повернуті значення описують рядки безпосередньо перед їх видаленням.
Для видалення одного рядка за ідентифікатором:
DELETE FROM tasks
WHERE id = 2
RETURNING *;Якщо рядка з таким ідентифікатором немає, результат буде порожнім.
Наведений скрипт можна виконати в PostgreSQL. Тимчасова таблиця існує лише протягом поточного з’єднання:
CREATE TEMPORARY TABLE products (
id integer GENERATED ALWAYS AS IDENTITY,
name text NOT NULL,
price numeric(10, 2) NOT NULL,
stock integer NOT NULL DEFAULT 0
);
-- Створюємо товар і одразу отримуємо згенерований ідентифікатор
INSERT INTO products (name, price, stock)
VALUES ('Клавіатура', 2499.00, 10)
RETURNING id, name, price, stock;
-- Створюємо ще два товари та отримуємо всі створені рядки
INSERT INTO products (name, price, stock)
VALUES
('Миша', 999.00, 25),
('Навушники', 3199.00, 7)
RETURNING *;
-- Змінюємо ціну товарів і отримуємо нові значення
UPDATE products
SET price = round(price * 0.9, 2)
WHERE stock > 5
RETURNING id, name, price;
-- Видаляємо товари без залишку та отримуємо видалені рядки
DELETE FROM products
WHERE stock = 0
RETURNING id, name, price, stock;Кожен оператор повертає власний набір рядків. Застосунок може прочитати цей результат так само, як результат звичайного SELECT.
RETURNING разом із ON CONFLICTRETURNING можна використовувати з INSERT ... ON CONFLICT. Це дає змогу отримати результат вставки або оновлення під час upsert-операції.
CREATE TEMPORARY TABLE user_settings (
user_id integer PRIMARY KEY,
theme text NOT NULL
);
INSERT INTO user_settings (user_id, theme)
VALUES (10, 'light')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme
RETURNING user_id, theme;EXCLUDED.theme — це значення, яке намагалися вставити. Секція RETURNING повертає підсумковий рядок після вставки або оновлення.
Якщо в DO UPDATE ... WHERE умова не виконується, конфліктний рядок не буде оновлено і не потрапить до результату RETURNING.
RETURNINGУ секції можна використовувати:
UPDATE products
SET stock = stock + 1
RETURNING id, stock;DELETE FROM products
WHERE id = 1
RETURNING *;UPDATE products
SET price = price * 1.05
RETURNING
id,
price,
round(price, 2) AS rounded_price;Назви, створені за допомогою AS, стають назвами стовпців у результаті.
Окремо отримати значення за замовчуванням після вставки можна саме через RETURNING:
INSERT INTO products (name, price)
VALUES ('Вебкамера', 4599.00)
RETURNING id, name, price, stock;Тут stock не передано в INSERT, тому результат покаже його значення за замовчуванням 0.
RETURNING і клієнтський кодНа рівні SQL результат RETURNING є набором рядків. Тому драйвер PostgreSQL зазвичай читає його як результат запиту.
Наприклад, у Node.js із пакетом pg:
import pg from 'pg';
const { Client } = pg;
const client = new Client({
connectionString: process.env.DATABASE_URL
});
await client.connect();
try {
const result = await client.query(
`
INSERT INTO products (name, price)
VALUES ($1, $2)
RETURNING id, name, price
`,
['Мікрофон', 2799.00]
);
// У rows містяться рядки, повернуті секцією RETURNING
console.log(result.rows[0]);
} finally {
await client.end();
}Для одного вставленого рядка result.rows[0] містить створений об’єкт разом із його ідентифікатором.
Параметри $1 і $2 передаються окремо від SQL-тексту. Це дає змогу безпечно працювати зі значеннями, отриманими від користувача.
RETURNING не замінює SELECT у всіх випадкахRETURNING повертає лише рядки, змінені конкретним INSERT, UPDATE або DELETE.
Наприклад, він не повертає всі рядки таблиці після виконання оновлення:
UPDATE products
SET stock = stock + 1
WHERE id = 1
RETURNING id, stock;Результат міститиме лише оновлений рядок із id = 1, а не всі товари.
Якщо потрібно отримати довільні рядки, які не були змінені цим оператором, використовується окремий SELECT.
WHERE в UPDATE або DELETERETURNING покаже всі рядки, які були змінені. Якщо випадково пропустити WHERE, можна змінити або видалити всю таблицю:
UPDATE products
SET price = 0
RETURNING id, name, price;Перед виконанням небезпечного оператора перевіряйте умову WHERE. Для особливо важливих змін спочатку виконайте аналогічний SELECT.
Навіть якщо логіка застосунку передбачає один рядок, RETURNING може повернути:
нуль рядків, якщо збігів немає;
один рядок;
кілька рядків, якщо умова відповідає кільком записам.
Код застосунку повинен обробляти ці випадки.
RETURNING для рядків, які не змінювалисяУмова:
UPDATE products
SET stock = stock
WHERE id = 1
RETURNING *;формально виконує UPDATE і повертає рядок, хоча значення фактично не змінилося. Якщо потрібно повертати лише рядки з реальною зміною, додайте відповідну умову, наприклад:
UPDATE products
SET price = 1999.00
WHERE id = 1
AND price IS DISTINCT FROM 1999.00
RETURNING *;IS DISTINCT FROM коректно працює також зі значеннями NULL.
Звичайний UPDATE ... RETURNING повертає значення рядка після оновлення. Якщо потрібно порівняти старі й нові значення, старі дані треба зберегти окремо, наприклад у CTE або отримати в межах іншої конструкції. Не слід очікувати, що простий RETURNING price покаже попередню ціну.
RETURNING додається до INSERT, UPDATE або DELETE.
Він повертає рядки, створені, оновлені або видалені цим оператором.
Через RETURNING зручно отримувати згенеровані ідентифікатори та значення за замовчуванням.
Можна повертати окремі стовпці, * і обчислювані вирази.
Якщо оператор не змінив жодного рядка, результат RETURNING буде порожнім.
RETURNING скорочує кількість запитів і допомагає атомарно отримати результат зміни даних.