Motivation / Background

This pull request adds support for RETURNING clauses to INSERT/UPDATE/DELETE statements in Arel. This is useful to build queries that modify data and return it (or something else) in the same query.

The following example builds a query that deletes all data from a counter queue table and returns the aggregated counter delta values.

counter_queue = Arel::Table.new(:counter_queue)
flush = Arel::Table.new(:flush)

delete_manager = Arel::DeleteManager.new 
  .from(counter_queue)
  .returning([counter_queue[:post_id], counter_queue[:delta]])

select_manager = Arel::SelectManager.new 
  .with(Arel::Nodes::TableAlias.new(Arel::Nodes::Grouping.new(delete_manager.ast), flush.name))
  .from(flush)
  .project(flush[:post_id], flush[:delta].sum) 
  .group(flush[:post_id])

WITH "flush" AS (
  DELETE FROM
    "counter_queue" 
  RETURNING 
    "counter_queue"."post_id",
    "counter_queue"."delta"
)
SELECT
  "flush"."post_id",
  SUM("flush"."delta")
FROM
  "flush"
GROUP BY
  "flush"."post_id"

Detail

To add support for RETURNING, this pull request adds a returning method to the sStatement and manager classes for insert, upsert, and delete. It also modifies the ToSql and Dot visitors to incorporate the returning statement (should there be one) into their output.

Additional information

The RETURNING syntax is supported in both PostgreSQL (INSERT, UPDATE, DELETE) and Sqlite3 (INSERT, UPDATE, DELETE). MySQL does not support RETURNING on any of these statements at the moment.

This pull request does not add RETURNING support to ActiveRecord itself (as discussed in #39968 and #42955). Nonetheless, I think this change to just Arel is useful in its own right to build more complex queries when needed.

Checklist

Before submitting the PR make sure the following are checked:

• This Pull Request is related to one change. Changes that are unrelated should be opened in separate PRs.
• Commit message has a detailed description of what changed and why. If this PR fixes a related issue include it in the commit message. Ex: [Fix #issue-number]
• Tests are added or updated if you fix a bug or add a feature.
• CHANGELOG files are updated for the changed libraries if there is a behavior change or additional feature. Minor bug fixes and documentation changes should not be included.