Getting back modified data with RETURNING

Each write-statement (INSERT, UPDATE, or DELETE) supports an optional RETURNING predicate that makes the statement return a results set with the manipulated tuples. From a conceptual point of view, it is as if these INSERT, UPDATE, and DELETE commands are immediately followed by an automatic SELECT statement.

This feature is very useful. It allows us to get back the exact values of the manipulated tuples, such as an automatically generated key, a computed timestamp, or other non-predictable values. It also allows us to pipeline write statements one after another, as you will see later in this chapter.

Let's take a look at the RETURNING function in action. Imagine that we need to insert some random data into the files table, as follows:

testdb=> INSERT INTO files( f_name, f_hash, f_size ) 
SELECT 'file_' || v || '.txt', md5( v::text ), v * ( random() * 100 )::int FROM generate_series(1, 10 ) v;

The preceding lines generate ten records with a random f_size and f_hash, as well as an auto-generated value for the pk primary key. We don't know the exact values that are going to hit, which are the values that are stored in the underlying table. RETURNING helps to solve this problem by providing us with the tuples inserted by the statement, as shown in the following listing:

testdb=> INSERT INTO files( f_name, f_hash, f_size )
SELECT 'File_' || v || '.txt',
       md5( v::text || random() ), v * ( random() * 111 )::int
FROM generate_series( 1, 10 ) v
RETURNING pk, f_name, f_hash, f_size, ts;

 pk |   f_name    |              f_hash              |  f_size  |             ts             
----+-------------+----------------------------------+----------+----------------------------
 24 | File_1.txt  | c09206052f182c8a01cd83ee0d4a7a78 |  21.0000 | 2018-10-31 09:37:29.036638
 25 | File_2.txt  | cec37633a67a66f99e4e427df5e40ee0 | 208.0000 | 2018-10-31 09:37:29.036638
 26 | File_3.txt  | afd08c4410e7600931bfcef8c3627cde | 267.0000 | 2018-10-31 09:37:29.036638
 27 | File_4.txt  | e8e56856ba183212b433151aeb3506cd | 384.0000 | 2018-10-31 09:37:29.036638
 28 | File_5.txt  | eab791d36b1fa25816d6715e628db02c | 235.0000 | 2018-10-31 09:37:29.036638
 29 | File_6.txt  | 552ed45e182088346cfd0503f2fef1f8 |  54.0000 | 2018-10-31 09:37:29.036638
 30 | File_7.txt  | 1a89ccc034a8d48b8bc92bf58d18e8bf | 679.0000 | 2018-10-31 09:37:29.036638
 31 | File_8.txt  | 0fe766ac50617ea7ff6d1cfb3e8060d2 | 400.0000 | 2018-10-31 09:37:29.036638
 32 | File_9.txt  | 063a175cf2b498dab6bf93fb8f76427a | 648.0000 | 2018-10-31 09:37:29.036638
 33 | File_10.txt | 42c450d54f4fe7e29b245a3d50258f4d | 770.0000 | 2018-10-31 09:37:29.036638

As we can see in the preceding snippet, the RETURNING function accepts the same column list as a regular SELECT command, including the special * symbol, which means the statement will return all available columns. The following listing illustrates how to use the * symbol to get back all the columns of the deleted tuples:

testdb=> DELETE FROM files RETURNING *;

 pk | f_name | f_hash |f_type|f_size| ts ----+-------------+----------------------------------+------+------+--------------------------- 1 | file_1.txt | c4ca4238a0b923820dcc509a6f75849b | | 54 | 2018-06-18 19:49:52.59167 2 | file_2.txt | c81e728d9d4c2f636f067f89cc14862c | | 78 | 2018-06-18 19:49:52.59167 3 | file_3.txt | eccbc87e4b5ce2fe28308fd9f2a7baf3 | | 153 | 2018-06-18 19:49:52.59167 4 | file_4.txt | a87ff679a2f3e71d9181a67b7542122c | | 280 | 2018-06-18 19:49:52.59167 5 | file_5.txt | e4da3b7fbbce2345d7772b0674a318d5 | | 160 | 2018-06-18 19:49:52.59167 6 | file_6.txt | 1679091c5a880faf6fb5e6087eb1b2dc | | 234 | 2018-06-18 19:49:52.59167 7 | file_7.txt | 8f14e45fceea167a5a36dedd4bea2543 | | 420 | 2018-06-18 19:49:52.59167 8 | file_8.txt | c9f0f895fb98ab9159f51fd0297e236d | | 232 | 2018-06-18 19:49:52.59167 9 | file_9.txt | 45c48cce2e2d7fbdea1afc51c7c6ad26 | | 396 | 2018-06-18 19:49:52.59167 10 | file_10.txt | d3d9446802a44259755d38e6d163e820 | | 280 | 2018-06-18 19:49:52.59167

Note that the result provided by the RETURNING function represents the final state of the manipulated tuples. This means that the result set is built after all of the triggers, if any, have been fired.

The result set provided by a RETURNING predicate is also available from external client applications. For example, as shown in the following listing, it is possible to iterate over results from a Java client:

class returning {
    public static void main( String argv[] ) throws Exception {
        Class.forName( "org.postgresql.Driver" );
        String connectionURL = "jdbc:postgresql://localhost/testdb";
        Properties connectionProperties = new Properties();
        connectionProperties.put( "user", "luca" );
        connectionProperties.put( "password", "secret" );
        Connection conn = DriverManager.getConnection( connectionURL, 
        connectionProperties );

        String query = "INSERT INTO files( f_name, f_hash, f_size ) "
            + " SELECT 'file_' || v || '.txt',"
            + " md5( v::text ),"
            + " v * ( random() * 100 )::int"
            + " FROM generate_series(1, 10 ) v "
            + " RETURNING pk, f_name, f_hash, f_size, ts;";

        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery( query );
        while ( resultSet.next() )
            System.out.println( String.format( "pk = %d, size = %d, 
            hash = %s", resultSet.getLong( "pk" ), resultSet.getInt( 
            "f_size" ), resultSet.getString( "f_hash" ) ) );
            resultSet.close();
            statement.close();
    }
}

When the Java code is executed, the output will look as follows:

pk = 11, size = 22, hash = c4ca4238a0b923820dcc509a6f75849b
pk = 12, size = 90, hash = c81e728d9d4c2f636f067f89cc14862c
pk = 13, size = 225, hash = eccbc87e4b5ce2fe28308fd9f2a7baf3
pk = 14, size = 368, hash = a87ff679a2f3e71d9181a67b7542122c
pk = 15, size = 460, hash = e4da3b7fbbce2345d7772b0674a318d5
pk = 16, size = 330, hash = 1679091c5a880faf6fb5e6087eb1b2dc
pk = 17, size = 140, hash = 8f14e45fceea167a5a36dedd4bea2543
pk = 18, size = 544, hash = c9f0f895fb98ab9159f51fd0297e236d
pk = 19, size = 801, hash = 45c48cce2e2d7fbdea1afc51c7c6ad26
pk = 20, size = 980, hash = d3d9446802a44259755d38e6d163e820