В ожидании 8.4 - RETURNING в подзапросах

Перевод Waiting for 8.4 - sql-wrappable RETURNING с select * from depesz;

В PostgreSQL 8.2 было добавлено выражение RETURNING для INSERT/UPDATE/DELETE запросов. К сожалению, оно не могло быть использовано как источник строк для всего в SQL.

insert into table_backup delete from table where ... returning *;

В прочем, это и сейчас не возможно, но был сделан один шаг в правильном направлении, благодаря патчу от Tom Lane 31го Октября:

Позволяет SQL-функциям возвращать вывод INSERT/UPDATE/DELETE RETURNING выражений, а не только SELECT как прежде.

Дополнительный эффект этого патча таков, что когда возвращающая множество SQL функция используется в FROM, производительность увеличивается за счёт того, что вывод накапливается в tuplestore внутри функции, в отличие от менее эффективного значение-за-вызов механизма.

Как это работает? Всё просто. Начнём с тестовой таблицы:

# create table test (i int4);
CREATE TABLE

С тестовым контентом:

# insert into test select generate_series(1, 10);
INSERT 0 10

Теперь создадим свою SQL функцию удаляющую строки:

CREATE function delete_from_test_returning(INT4) RETURNS setof test as $$
    DELETE FROM test WHERE i <= $1 returning *
$$ language sql;

Как видно, функция очень проста.

Теперь используем её для бэкапа удаленных строк:

# create table delete_backup as select * from delete_from_test_returning(3);
SELECT

И проверим содержание обеих таблиц:

# select * from test;
 i
----
  4
  5
  6
  7
  8
  9
 10

(7 rows)

# select * from delete_backup;
 i
---
 1
 2
 3

(3 rows)

Конечно, я мог бы сделать это раньше в pl/pgsql функции, которая бы пробегалась по всем возвращаемым строкам, но в данном случае это определённо будет быстрее.