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.