select * from depesz;

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
value-per-call mechanism.

How does it work? It’s pretty simple. Let’s start with test table:

# create table test (i int4);
CREATE TABLE

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);
SELECT

And check the content of tables:

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

# select * from delete_backup;
i
---
1
2
3
(3 rows)

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.