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.