Returning Clause

PostgresqlDatabase supports a RETURNING clause on UPDATE,INSERT and DELETE queries. Specifying a RETURNING clause allows youto 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 newrow’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 bean iterable cursor object.

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

For example, let’s say you have an Update that deactivates alluser accounts whose registration has expired. After deactivating them, you wantto send each user an email letting them know their account was deactivated.Rather than writing two queries, a SELECT and an UPDATE, you can dothis 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 andDelete. When used with INSERT, the newly-created rows will bereturned. When used with DELETE, the deleted rows will be returned.

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

As another example, let’s add a user and set their creation-date to theserver-generated current timestamp. We’ll create and retrieve the new user’sID, 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 canspecify 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.