Writable CTEs and the RETURNING clause: Pipelining statements

We can combine writable CTEs and the RETURNING clause to create a quick pipeline of SQL statements. As a practical example, let's suppose we need to archive all file entries to another table with the same structure that we will use in a historic archive. We will call this table archive. Since SQL does not allow move statements, a traditional approach would be to perform a two-step transaction, as shown in the following listing, where an INSERT statement is executed and the moved entries are deleted:

testdb=> BEGIN; 
INSERT INTO archive_files
SELECT * FROM files; 
DELETE FROM files;  -- can use also a TRUNCATE 
COMMIT;

With CTEs, we can also attack the problem from another angle; we can define a writable CTE to perform the entry deletion and return all of the deleted records. These records will materialize and can then be pushed into an INSERT statement. Since the CTE will execute as a single whole statement, transaction boundaries will apply to it (in auto-commit mode). This means that the operation with either succeed or fail as a whole. In the latter case, it won't delete any entries from the source table, as shown in the following listing:

testdb=> WITH deleting_files AS ( DELETE FROM files RETURNING * ) 
INSERT INTO archive_files SELECT * FROM deleting_files;

CTEs are also flexible enough to allow for the opposite to happen, as shown in the following listing, where an INSERT statement performs as an auxiliary table and a DELETE statement as the top-level statement:

testdb=> WITH inserting_files AS ( 
INSERT INTO files_archive 
SELECT * FROM files RETURNING pk ) 
DELETE FROM files 
WHERE pk IN ( SELECT pk FROM inserting_files );