Returning Clause

PostgresqlDatabase supports a RETURNING clause on UPDATE, INSERT and DELETE queries. Specifying a RETURNING clause allows you to iterate over the rows accessed by the query.

By default, the return values upon execution of the different queries are:

• INSERT - auto-incrementing primary key value of the newly-inserted row. When not using an auto-incrementing primary key, Postgres will return the new row’s primary key, but SQLite and MySQL will not.
• UPDATE - number of rows modified
• DELETE - number of rows deleted

When a returning clause is used the return value upon executing a query will be an iterable cursor object.

Postgresql allows, via the RETURNING clause, to return data from the rows inserted or modified by a query.

For example, let’s say you have an Update that deactivates all user accounts whose registration has expired. After deactivating them, you want to send each user an email letting them know their account was deactivated. Rather than writing two queries, a SELECT and an UPDATE, you can do this in a single UPDATE query with a RETURNING clause:

query = (User
         .update(is_active=False)
         .where(User.registration_expired == True)
         .returning(User))
# Send an email to every user that was deactivated.
for deactivate_user in query.execute():
    send_deactivation_email(deactivated_user.email)

The RETURNING clause is also available on Insert and Delete. When used with INSERT, the newly-created rows will be returned. When used with DELETE, the deleted rows will be returned.

The only limitation of the RETURNING clause is that it can only consist of columns from tables listed in the query’s FROM clause. To select all columns from a particular table, you can simply pass in the Model class.

As another example, let’s add a user and set their creation-date to the server-generated current timestamp. We’ll create and retrieve the new user’s ID, Email and the creation timestamp in a single query:

query = (User
         .insert(email='foo@bar.com', created=fn.now())
         .returning(User))  # Shorthand for all columns on User.
# When using RETURNING, execute() returns a cursor.
cursor = query.execute()
# Get the user object we just inserted and log the data:
user = cursor[0]
logger.info('Created user %s (id=%s) at %s', user.email, user.id, user.created)

By default the cursor will return Model instances, but you can specify a different row type:

data = [{'name': 'charlie'}, {'name': 'huey'}, {'name': 'mickey'}]
query = (User
         .insert_many(data)
         .returning(User.id, User.username)
         .dicts())
for new_user in query.execute():
    print('Added user "%s", id=%s' % (new_user['username'], new_user['id']))

Just as with Select queries, you can specify various result row types.