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.
Continue reading Waiting for 8.4 – sql-wrappable RETURNING