Waiting for 8.4 – sql-wrappable RETURNING

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.