In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.
Unfortunately it could not be used as source of rows for anything in sql.
insert into table_backup delete from table where ... returning *;
Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:
Allow SQL-language functions to return the output of an INSERT/UPDATE/DELETE
RETURNING clause, not just a SELECT as formerly.
A side effect of this patch is that when a set-returning SQL function is used
in a FROM clause, performance is improved because the output is collected into
a tuplestore within the function, rather than using the less efficient
How does it work? It's pretty simple. Let's start with test table:
# create table test (i int4);
With some test content:
# insert into test select generate_series(1, 10);
INSERT 0 10
Now, let's create our sql function which will delete rows:
CREATE function delete_from_test_returning(INT4) RETURNS setof test as $$
DELETE FROM test WHERE i <= $1 returning *
$$ language sql;
As you can see the function is pretty simple.
Now, let's use it to backup removed rows:
# create table delete_backup as select * from delete_from_test_returning(3);
And check the content of tables:
# select * from test;
# select * from delete_backup;
Of course I could have done it before with pl/PgSQL function that would iterate over returned rows, but this approach will be definitely faster.